# Formula for the max value of a sumif

#### nruk123

##### New Member
I need a formula that looks at the two columns below and gives me the result "Wed" as the max value

Mon 10
Tue 15
Wed 20
Mon 15
Tue 10
Wed 20

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Hi. Try this entered CTRL-SHIFT-ENTER:

=INDEX(A1:A6,MATCH(MAX(SUMIFS(B1:B6,A1:A6,A1:A6)),SUMIFS(B1:B6,A1:A6,A1:A6),0))

Excel 2013/2016
ABC
2Mon10Wed
3Tue15
4Wed20
5Mon15
6Tue10
7Wed20
Cover
Cell Formulas
RangeFormula
C2=INDEX(A2:A7,MATCH(MAX(B2:B7),B2:B7,0))

Last edited:
That one nearly works. I want it to come back with Wed as the sum 40 is the highest total

Excel 2013/2016
ABC
2Mon10Wed
3Tue15
4Wed20
5Mon15
6Tue10
7Wed20

</tbody>
Cover

Worksheet Formulas
CellFormula
C2=INDEX(A2:A7,MATCH(MAX(B2:B7),B2:B7,0))

</tbody>

<tbody>
</tbody>

That wouldnt quite work for a sumif test though fluff.

I can do it easily with a pivot table but would prefer a formula to do it for me

I already gave you a formula? See post 2.

If you dont like CTRL-SHIFT-ENTER then maybe this:

=LOOKUP(2,1/((MAX(INDEX(SUMIFS(B1:B6,A1:A6,A1:A6),0))=SUMIFS(B1:B6,A1:A6,A1:A6))),A1:A6)

That wouldnt quite work for a sumif test though fluff.

Agreed, I misunderstood the request.

You read my mind!! Never used array formulas! This one is awesome

If you dont like CTRL-SHIFT-ENTER then maybe this:

=LOOKUP(2,1/((MAX(INDEX(SUMIFS(B1:B6,A1:A6,A1:A6),0))=SUMIFS(B1:B6,A1:A6,A1:A6))),A1:A6)

Replies
7
Views
184
Replies
10
Views
200
Replies
4
Views
76
Replies
6
Views
207
Replies
6
Views
198

1,203,101
Messages
6,053,530
Members
444,670
Latest member
laurenmjones1111

### 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