Find max value of sumifs

[mrblister]

	A	B	C
1	Name	Points	Current Leader
2	Amy	10	Amy
3	Bob	5	Amy
4	Amy	2	Amy
5	Caleb	15	Caleb
6	Bob	10	Caleb
7	Bob	1	Bob
8	Amy	4	Bob
9	Bob	1	Bob
10	Amy	2	Amy

<tbody>
</tbody>

I'm trying to find a formula for column C, which outputs the person with the greatest accumulated score (accumulates moving down. E.g. C5 outputs the person with the highest score between rows 1 and 5). Notes:

• If there is a tie (e.g. C6, C8), column C returns the incumbent name (the name higher on the list) OR can display "Tie"
• I'd rather not use helper columns FOR NAMES if possible, as I have many names. But helper columns would be OK, just not for names.

Thanks!

 

[Eric W]

Try this in C2:

=INDEX($A$1:$A2,MATCH(MAX(SUMIF($A$1:$A2,$A$1:$A2,$B$1:$B2)),SUMIF($A$1:$A2,$A$1:$A2,$B$1:$B2),0))

confirm with Control+Shift+Enter, then drag down as needed.

 

[jtakw]

Hi,

Since it's accumulated scores going down column...

Use C2 formula copied down if you want to show "Tie" (Your sample row 6 where you show "Caleb" should be a Tie between Bob and Caleb ?)
Use D2 formula copied down if you Don't need to show "Tie", as in your posted sample.

Formulas normally entered:

Book1
	A	B	C	D
1	Name	Points	Current Leader
2	Amy	10	Amy	Amy
3	Bob	5	Amy	Amy
4	Amy	2	Amy	Amy
5	Caleb	15	Caleb	Caleb
6	Bob	10	Tie	Caleb
7	Bob	1	Bob	Bob
8	Amy	4	Tie	Bob
9	Bob	1	Bob	Bob
10	Amy	2	Amy	Amy
11	Bob	1	Tie	Amy
Sheet626

Cell Formulas
Range	Formula
C2	=IF(SUMIF(A$2:A2,A2,B$2:B2)=SUMIF(A$1:A1,A1,B$1:B1),"Tie",IF(SUMIF(A$2:A2,A2,B$2:B2)>SUMIF(A$1:A1,A1,B$1:B1),A2,C1))
D2	=IF(SUMIF(A$2:A2,A2,B$2:B2)>SUMIF(A$1:A1,A1,B$1:B1),A2,C1)