Formula for the max value of a sumif

[nruk123]

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

 

[steve the fish]

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

 

[Fluff]

How about

Excel 2013/2016
	A	B	C
2	Mon	10	Wed
3	Tue	15
4	Wed	20
5	Mon	15
6	Tue	10
7	Wed	20
Cover

Cell Formulas
Range	Formula
C2	=INDEX(A2:A7,MATCH(MAX(B2:B7),B2:B7,0))

 

[nruk123]

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

How about

Excel 2013/2016

	A	B	C

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]Wed[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Tue[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Tue[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

</tbody>

Cover​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=INDEX(A2:A7,MATCH(MAX(B2:B7),B2:B7,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

That wouldnt quite work for a sumif test though fluff.

 

[nruk123]

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

 

[steve the fish]

I already gave you a formula? See post 2.

 

[steve the fish]

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)

 

[Fluff]

That wouldnt quite work for a sumif test though fluff.

Agreed, I misunderstood the request.

 

[nruk123]

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)