Find max value of sumifs

[mrblister]

[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64, align: left"]A[/TD]
[TD="width: 64, align: left"]B[/TD]
[TD="width: 64, align: left"]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]Name[/TD]
[TD="align: left"]Points[/TD]
[TD="align: left"]Current Leader[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]Amy[/TD]
[TD="align: right"]10[/TD]
[TD="align: left"]Amy[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: left"]Bob[/TD]
[TD="align: right"]5[/TD]
[TD="align: left"]Amy[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: left"]Amy[/TD]
[TD="align: right"]2[/TD]
[TD="align: left"]Amy[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: left"]Caleb[/TD]
[TD="align: right"]15[/TD]
[TD="align: left"]Caleb[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: left"]Bob[/TD]
[TD="align: right"]10[/TD]
[TD="align: left"]Caleb[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: left"]Bob[/TD]
[TD="align: right"]1[/TD]
[TD="align: left"]Bob[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: left"]Amy[/TD]
[TD="align: right"]4[/TD]
[TD="align: left"]Bob[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: left"]Bob[/TD]
[TD="align: right"]1[/TD]
[TD="align: left"]Bob[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: left"]Amy[/TD]
[TD="align: right"]2[/TD]
[TD="align: left"]Amy[/TD]
[/TR]
</tbody>[/TABLE]

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)