5

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.

Oliver Salzburg
  • 89,072
  • 65
  • 269
  • 311
Shevek
  • 16,738

2 Answers2

3

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

barry houdini
  • 11,212
1

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 )
Shekhar
  • 5,139