Summing and Top/Bottom Results

[Graemea]

Hi,

I have sample data in A:B and would like in columns D:E to extract from this the top and bottom 5 products by total sales.

The results that I would expect from my sample data are shown in D:E in the screenshot below.

The sales for each product is simply the sum of the Sales in column B.

Getting the top and bottom 5 products by total sales can easily by done with a Pivot Table but a formula solution would be far more helpful for my purposes.

Is there a formula that someone could please suggest?

Thanks!

 

[Eric W]

One way:

Book5
	A	B	C	D	E
1	Product	Sales		TOP 2
2	AAA	100		DDD	800
3	BBB	300		BBB	600
4	CCC	200
5	DDD	400		BOTTOM 2
6	AAA	100		AAA	200
7	BBB	300		CCC	400
8	CCC	200
9	DDD	400
10
Sheet12

Cell Formulas
Range	Formula
D2:D3	D2	=INDEX($A$2:$A$9,MATCH(MAX(IF(COUNTIF($D$1:$D1,$A$2:$A$9)=0,SUMIF($A$2:$A$9,$A$2:$A$9,$B$2:$B$9))),IF(COUNTIF($D$1:$D1,$A$2:$A$9)=0,SUMIF($A$2:$A$9,$A$2:$A$9,$B$2:$B$9)),0))
E6:E7,E2:E3	E2	=SUMIF($A$2:$A$9,D2,$B$2:$B$9)
D6:D7	D6	=INDEX($A$2:$A$9,MATCH(MIN(IF(COUNTIF($D$1:$D5,$A$2:$A$9)=0,SUMIF($A$2:$A$9,$A$2:$A$9,$B$2:$B$9))),IF(COUNTIF($D$1:$D5,$A$2:$A$9)=0,SUMIF($A$2:$A$9,$A$2:$A$9,$B$2:$B$9)),0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Graemea]

That's fantastic, thanks very much Eric!

 

[Graemea]

Can I please ask one more quick question.

Can someone please suggest how the above formulae can be adapted to exclude certain product codes.

For example, if the product code is "GGG" or "MMM", it will be ignored from the determination of the top or bottom 5 products; if a product is excluded, then the next largest, or smallest, will take its place.

Thanks!

 

[mikerickson]

With that many moving parts, you might want to look at a pivot table.

 

[Eric W]

Maybe:

Book5
	A	B	C	D	E	F	G
1	Product	Sales		TOP 2			Exclude
2	AAA	100		BBB	600		AAA
3	BBB	300		CCC	400		DDD
4	CCC	200
5	DDD	400		BOTTOM 2
6	AAA	100		CCC	400
7	BBB	300		BBB	600
8	CCC	200
9	DDD	400
Sheet12

Cell Formulas
Range	Formula
D2:D3	D2	=IFERROR(INDEX($A$2:$A$9,MATCH(MAX(IF((COUNTIF($D$1:$D1,$A$2:$A$9)=0)*ISERROR(MATCH($A$2:$A$9,$G$2:$G$10,0)),SUMIF($A$2:$A$9,$A$2:$A$9,$B$2:$B$9))),IF((COUNTIF($D$1:$D1,$A$2:$A$9)=0)*ISERROR(MATCH($A$2:$A$9,$G$2:$G$10,0)),SUMIF($A$2:$A$9,$A$2:$A$9,$B$2:$B$9)),0)),"")
E6:E7,E2:E3	E2	=SUMIF($A$2:$A$9,D2,$B$2:$B$9)
D6:D8	D6	=IFERROR(INDEX($A$2:$A$9,MATCH(MIN(IF((COUNTIF($D$5:$D5,$A$2:$A$9)=0)*ISERROR(MATCH($A$2:$A$9,$G$2:$G$10,0)),SUMIF($A$2:$A$9,$A$2:$A$9,$B$2:$B$9))),IF((COUNTIF($D$5:$D5,$A$2:$A$9)=0)*ISERROR(MATCH($A$2:$A$9,$G$2:$G$10,0)),SUMIF($A$2:$A$9,$A$2:$A$9,$B$2:$B$9)),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Although Mike has a point. This formula is getting pretty unwieldy. You can make a pivot table in just a few seconds, although I couldn't quite figure out how to get it to sort by the Sum of Sales column.

 

[Graemea]

That's great, thank you very much!