# Excel: Counting number of MAX values in a range

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.

-

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.....]

-
I wish I could give you +10 for spotting the film reference :) – Shevek Jan 14 '12 at 14:54
It works! Now I just need to understand it ;) – Shevek Jan 16 '12 at 13:35
The OFFSET technique is explained in this link - dailydoseofexcel.com/archives/2005/05/11/arrays-with-offset - albeit in a slightly different context. That's using OFFSET to split one column into an "array of ranges" where each range is one cell.....then SUBTOTAL is used to see if that cell is filtered or not. My version uses OFFSET in the same way but the ranges are rows rather than single cells. SUBTOTAL is then used with 4 for the MAX. You can also use COUNTIF rather than SUBTOTAL to count how many values in each row are larger (if none you have the MAX)...but that's longer..... – barry houdini Jan 16 '12 at 15:46
Many thanks...! – Shevek Jan 17 '12 at 19:42

Add a new column next to BOB with the formula ``

``````=MAX(B2,C2,D2)
``````

For the Total cell put the following

``````=SUM(IF(B2=\$E2,1,0),IF(B3=\$E3,1,0),IF(B4=\$E4,1,0))
``````

NOTE: all formula are based on assumption that the cell `Round` is `A1`
(also I have tried this out on office 2010 so sont know if exact functions are available in 2003

EDIT
If you dont want an extra column you could combine the two formulae in this fashion

``````=SUM(IF(B2=MAX(B2,C2,D2),1,0),IF(B3=MAX(B3,C3,D3),1,0), and so on )
``````
-
That's sort of the solution I have in place. I was really looking for something which can be done without adding extra columns. – Shevek Jan 13 '12 at 14:18
@Shevek hope this works out for you – Shekhar Jan 13 '12 at 14:48