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

    =SUMPRODUCT((SUBTOTAL(4,OFFSET($B$2:$D$4,ROW($B$2:$D$4)-ROW(B$2),0,1))=B2:B4)+0)

    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