Find max value of sumifs

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
177
Office Version
  1. 2019
Platform
  1. Windows
ABC
1NamePointsCurrent Leader
2Amy10Amy
3Bob5Amy
4Amy2Amy
5Caleb15Caleb
6Bob10Caleb
7Bob1Bob
8Amy4Bob
9Bob1Bob
10Amy2Amy

<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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,325
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

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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
1NamePointsCurrent Leader
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,874
Members
431,771
Latest member
CoryMelth

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top