# Find max value of sumifs

#### mrblister

##### Board Regular
 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!

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.

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
ABCD
2Amy10AmyAmy
3Bob5AmyAmy
4Amy2AmyAmy
5Caleb15CalebCaleb
6Bob10TieCaleb
7Bob1BobBob
8Amy4TieBob
9Bob1BobBob
10Amy2AmyAmy
11Bob1TieAmy
Sheet626
Cell Formulas
RangeFormula
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)

Replies
5
Views
204
Replies
20
Views
311
Replies
11
Views
298
Replies
9
Views
734
Replies
8
Views
82

1,203,453
Messages
6,055,530
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back