5 largest sums and corresponding value

[Nebula2121]

Hi. I need help. Example Data in column A - C. Desired Results E-F. I am looking to return the 5 largest sums based on a column and return the corresponding value. Thoughts?

A	B	C	D	E	F
1	FL	200		MN	700
2	TX	200		CO	500
3	FL	100		FL	400
4	FL	100		TX	400
5	MN	400		CA	200
6	MA	100
7	TX	200
8	CA	100
9	CA	100
10	CO	500
11	MN	300
12	NY	100

<tbody>
</tbody>

 

[nemmi69]

do you want this in VBa?

 

[Nebula2121]

Thank you for asking. No VBA is allowed. Formula based only.

 

[nemmi69]

well you could use consolidate to combine all the amounts for each state then sort for high to low. Or use large to obtain top 5 IE =Large(range,1),=Large(range,2)=Large(range,3),=Large(range,4),=Large(range,5)

 

[Nebula2121]

I wish it was that simple. Not allowed to sort.

 

[Bratamoli]

My first recommendation would be to create a SUMIF table in columns H & I (or wherever).
I'm pretty good with excel formulas, but summing each state and finding the top 5 in one fluid step would be ... "difficult."

Then I would use this to get the "Top 5":
F1 would be =LARGE(I:I,1)
F5 would be =LARGE(I:I,5)
**assuming each state is in column H & each total is in column I

Then I would use ↓this↓ to create your outputs in column E based on the top 5 in column F

Try this...

PS - You can click the arrows next to Valko's name to go to his post in another thread. Just in case you hadn't realized that yet or something.

 

[Aladin Akyurek]

Book1
	B	C	D	E	F
1	state	value		N	5
2	FL	200		state count	7
3	TX	200		Nth Value	200
4	FL	100		adjusted N	5
5	FL	100		state	total value
6	MN	400		MN	700
7	MA	100		CO	500
8	TX	200		FL	400
9	CA	100		TX	400
10	CA	100		CA	200
11	CO	500
12	MN	300
13	NY	100
Sheet1

F1: 5 (desired Top N)

In F2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$13=""),MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1),1))

In F3 control+shift+enter, not just enter:

=LARGE(SUMIFS($C$2:$C$13,$B$2:$B$13,IF(FREQUENCY(IF(1-($B$2:$B$13=""),MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1),$B$2:$B$13)),MIN(F1,F2))

In F4 control+shift+enter, not just enter:

=SUM(IF(SUMIFS($C$2:$C$13,$B$2:$B$13,IF(FREQUENCY(IF(1-($B$2:$B$13=""),MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1),$B$2:$B$13))>=F3,1))

This adjusts N for ties if applicable.

In E6 control+shift+enter, not just enter, and copy down:

=IF($F6="","",INDEX($B$2:$B$13,SMALL(IF(SUMIFS($C$2:$C$13,$B$2:$B$13,IF(FREQUENCY(IF(1-($B$2:$B$13=""),MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1),$B$2:$B$13))=$F6,ROW($B$2:$B$13)-ROW($B$2)+1),COUNTIFS($F$6:F6,F6))))

In F6 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$6:F6)>$F$4,"",LARGE(SUMIFS($C$2:$C$13,$B$2:$B$13,IF(FREQUENCY(IF(1-($B$2:$B$13=""),MATCH($B$2:$B$13,$B$2:$B$13,0)),ROW($B$2:$B$13)-ROW($B$2)+1),$B$2:$B$13)),ROWS($F$6:F6)))

 

[nemmi69]

I used consolidate to create Columns E & F

I used =INDEX($E$2:$F$8,MATCH(LARGE($F$2:$F$8,1),$F$2:$F$8,0),1) in H2 and =LARGE($F$2:$F$8,1) in I2 to show largest state and amount
I used =INDEX($E$2:$F$8,MATCH(LARGE($F$2:$F$8,2),$F$2:$F$8,0),1) in H2 and =LARGE($F$2:$F$8,2) in I3 to show 2nd largest state and amount and so on