Find max value of sumifs

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
162
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!
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Name</td><td style=";">Points</td><td style=";">Current Leader</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Amy</td><td style="text-align: right;;">10</td><td style=";">Amy</td><td style=";">Amy</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Bob</td><td style="text-align: right;;">5</td><td style=";">Amy</td><td style=";">Amy</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Amy</td><td style="text-align: right;;">2</td><td style=";">Amy</td><td style=";">Amy</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Caleb</td><td style="text-align: right;;">15</td><td style=";">Caleb</td><td style=";">Caleb</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Bob</td><td style="text-align: right;;">10</td><td style=";">Tie</td><td style=";">Caleb</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Bob</td><td style="text-align: right;;">1</td><td style=";">Bob</td><td style=";">Bob</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Amy</td><td style="text-align: right;;">4</td><td style=";">Tie</td><td style=";">Bob</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Bob</td><td style="text-align: right;;">1</td><td style=";">Bob</td><td style=";">Bob</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Amy</td><td style="text-align: right;;">2</td><td style=";">Amy</td><td style=";">Amy</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Bob</td><td style="text-align: right;;">1</td><td style=";">Tie</td><td style=";">Amy</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet626</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">SUMIF(<font color="Red">A$2:A2,A2,B$2:B2</font>)=SUMIF(<font color="Red">A$1:A1,A1,B$1:B1</font>),"Tie",IF(<font color="Red">SUMIF(<font color="Green">A$2:A2,A2,B$2:B2</font>)>SUMIF(<font color="Green">A$1:A1,A1,B$1:B1</font>),A2,C1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">SUMIF(<font color="Red">A$2:A2,A2,B$2:B2</font>)>SUMIF(<font color="Red">A$1:A1,A1,B$1:B1</font>),A2,C1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,049
Messages
5,526,488
Members
409,703
Latest member
nbkqsj7

This Week's Hot Topics

Top