Excel: Counting number of MAX values in a range

countifmicrosoft excelworksheet-function

I am using Excel 2003 (version limited due to corporate policy) to keep a score sheet:

     A       B     C     D
1  Round | Rita | Sue | Bob
2    1   |   5  |  3  |  4
3    2   |   2  |  2  |  5
4    3   |   3  |  1  |  3

What I want is for each person to have a total of winning rounds, with a tie awarded to both. So the total row should look something like this:

 5  Total |   2  |  0  |  2

Is this possible in a single formula for a total?

I have tried combinations of CountIf and Max but can't quite get the desired output.

My only other solution is to have a separate set of score columns: e.g IF(B2=MAX(B2:D2),1,0) for each Round and then sum these for each person.

EDIT: To make it clear, I have a solution which involves adding extra columns. I am asking for solutions which allow formula(e) to be added to each existing person column to calculate the totals without adding any extra columns.

Best Answer

  • Try this formula in B5 copied across to D5, it'll give you the totals for Rita, Sue and Bob too


    This solution can be easily extended to larger ranges, i.e. with more rows or columns.....

    [OFFSET extracts each row as a separate range and SUBTOTAL with first argument 4 finds the MAX of each range.....]

  • Related Question