Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
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
1  
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 )
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.