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
  • 86,445
  • 63
  • 260
  • 306
Shevek
  • 16,502
  • 7
  • 46
  • 75
  • My suggestion needs extra columns, so not posting as an answer. You can tidy up that IF by using RANK(B2,$B2:$D2,0) instead. Highest score ranks 1, then 2, then 3. Ties rank 1,1,3 or 1,2,2. So then just use COUNTIF to look for the 1s, as you were planning to do. – AdamV Jun 26 '19 at 22:37

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
  • 10,892
  • 1
  • 20
  • 25
  • 1
    The OFFSET technique is explained in this link - http://www.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
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,009
  • 4
  • 33
  • 49
  • 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