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
2	Mon	10	Wed
3	Tue	15
4	Wed	20
5	Mon	15
6	Tue	10
7	Wed	20

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Cover​

Worksheet Formulas Cell Formula C2 =INDEX(A2:A7,MATCH(MAX(B2:B7),B2:B7,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

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