Q: EXCEL - Count number of times a value is the max of its category 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!

A: 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.

