I am trying to find the total number of times the search term (B2) is the maximum for each column.
For example, I can visually tell that row4 is the maximum in three different columns. However, my goal is to get this result with a formula for cases where there may be too many categories to manually count.
| column1 | column2 | column3 | column4 | column5 | column6 | |
|---|---|---|---|---|---|---|
| row1 | 0.00% | 0.15% | 0.00% | 0.04% | 0.01% | 0.08% |
| row2 | 0.00% | 0.00% | 0.00% | 0.04% | 0.00% | 823.45% |
| row3 | 2.45% | 0.98% | 0.29% | 1.45% | 0.24% | 0.66% |
| row4 | 14.04% | 4.97% | 12.02% | 10.32% | 13.76% | 6.31% |
| row5 | 0.00% | 0.00% | 0.12% | 15.23% | 0.11% | 0.00% |
| row6 | 1.71% | 5.12% | 2.75% | 0.98% | 1.02% | 1.34% |
Without added rows
I can do this fairly easily if I were to add another row, but my hope is to do it without this step.
With added row
Formulas used in the added rows:
=INDEX($D$3:$D$8,MATCH(MAX(E13:E18),E13:E18,0))
=COUNTIF(E19:J19,B12)
My guess is that I will need to use some type of sum/sumif function in order to add 1 for each occurrence of a column max, but I cannot seem to get it to work. I can return a boolean of 1 or 0 for a single occurrence, but I'm struggling to make it work through each column individually. For a single column, =IF(XLOOKUP(B2,D3:D8,E3:E8)>=MAX(E3:E8),"1","0") is working to set it to either 1 or 0 depending on if the search term is greater than or equal to the max. If I can make this formula work for each column, then sum it up, I believe it would work. Maybe an array is needed?
Any help is greatly appreciated!
CodePudding user response：
If you have Excel 365, you can use:
=SUM(--(A2=INDEX(myTbl[row],BYCOL(myTbl[[column1]:[column6]],LAMBDA(array,MATCH(MAX(array),array,0))))))
where A2 contains the name in the "row" column, this formula will return the number of columns where that row is MAX.
