Formula to find the number of times a row has the max value of the column

[cheung]

I was trying to find a way to count the number of times a given row has the maximum value in the column. Was that confusing enough? Basically, in rows I have scores for a given group over a ten week period. For each week there is one or more groups which have the highest score, and I'd like to know how many times each group "won" the week over that ten week period.

I attached an example spreadsheet in which I've highlighted the highest value for each week column (using conditional formating) and (manually) counted the times that group had the highest value. Is there a way to automate this (I have a larger data set and would like to not have to do it manually).

Groups.xlsx

[Sam Capricci]

would something like =COUNTIF(C3:L3,MAX(C3:L3)) if you are doing it across rows or =COUNTIF(C3:C17,MAX(C3:C17)) if you are doing it down columns work for you?

[playaller]

Hello Cheung,

Paste the below code in B3 and copy down:

Please Login or Register  to view this content.

[Tony Valko]

Try this...

This formula entered in B3 and copied down as needed:

=SUMPRODUCT(--(C3:L3=SUBTOTAL(4,OFFSET(C$3:C$17,,COLUMN($C$3:$L$17)-COLUMN($C$3)))))

[cheung]

would something like =COUNTIF(C3:L3,MAX(C3:L3)) if you are doing it across rows or =COUNTIF(C3:C17,MAX(C3:C17)) if you are doing it down columns work for you?

That's what I tried as well (made sense in my head), but it was definitely not working for me.

Hello Cheung,

Paste the below code in B3 and copy down:

Please Login or Register  to view this content.

This worked great! The only issue is that my data set is much larger than just 10 columns, more like 60 and growing, and while I would only have to do it once and then drag down this is a big formula that involves a lot of clicking and typing. I was hoping for something simpler like...

Try this...

This formula entered in B3 and copied down as needed:

=SUMPRODUCT(--(C3:L3=SUBTOTAL(4,OFFSET(C$3:C$17,,COLUMN($C$3:$L$17)-COLUMN($C$3)))))

... this! This worked perfectly and in addition it was easy to adapt to my real data and short and simple enough for me to understand what it was doing. Nice job, and thanks for the help!

[cheung]

One modification I made... when I applied it to my data set I had a lot of empty cells (which I will be filling in the future) and the formula Tony gave me was counting these having the max values for the column (technically true, I guess). To solve this problem I just added "-COUNTBLANK(range of row values)" after the formula, so that it would be:

Please Login or Register  to view this content.

[Tony Valko]

Good deal. Thanks for the feedback!