Sum column values based on multiple criterias - 2 columns & 1 row

[AnaLib]

Hi! Really need some urgent help with this I need to look up Unit Sales & Retail Sales by Collection but only for the top 10 or bottom - I don't want to SUM all data. Normally I've used a formula like SUM(IF(),IF(,))) but this time I don't know how to add in an additional criteria (Top 10/Bottom). Apologies for the bad cut and paste wanted to add an image or excel file but not sure how to.

COLLECTION	TOP 10	BOTTOM 10	UNIT SALES	RETAIL SALES $
Class 5406 MOVE UNO -MS			4	7950		TOP 10 ONLY				BOTTOM 10 ONLY
Class 5406 MOVE UNO -MS	1		0	0
Class 5407 BABY MOVE - MS			1	3380		Collection	Unit Sales	RETAIL SALES $		Collection	Unit Sales	RETAIL SALES $
Class 5406 MOVE UNO -MS		4	3	5912		Class 5406 MOVE UNO -MS				Class 5406 MOVE UNO -MS
Class 5407 BABY MOVE - MS		-	1	1751		Class 5407 BABY MOVE - MS				Class 5407 BABY MOVE - MS
Class 5406 MOVE UNO -MS			0	0
Class 5406 MOVE UNO -MS	4		4	8880
Class 5407 BABY MOVE - MS	5		1	2400
Class 5406 MOVE UNO -MS			3	3192
Class 5406 MOVE UNO -MS		1	2	2230
Class 5407 BABY MOVE - MS		-	0	0
Class 5406 MOVE UNO -MS			1	1120
Class 5407 BABY MOVE - MS		-	0	0
Class 5406 MOVE UNO -MS	2		1	1110
Class 5406 MOVE UNO -MS	3		0	0
Class 5407 BABY MOVE - MS		2	10	26103
Class 5407 BABY MOVE - MS	6		3	7696
Class 5407 BABY MOVE - MS	7		2	4120

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

 

[Aladin Akyurek]

Try to run a pivot table which allows you to identify Top N items.

 

[AnaLib]

Try to run a pivot table which allows you to identify Top N items.

Thanks but I need a formula!

 

[AnaLib]

Thanks but I need a formula!

Can anyone help with this?

 

[jtakw]

Hi,

What constitutes TOP 10 and BOTTOM 10, are we to rely on Columns B & C for that information?
If so, try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	COLLECTION	TOP 10	BOTTOM 10	UNIT SALES	RETAIL SALES $
2	Class 5406 MOVE UNO -MS			4	7950		TOP 10 ONLY				BOTTOM 10 ONLY
3	Class 5406 MOVE UNO -MS	1		0	0
4	Class 5407 BABY MOVE - MS			1	3380		Collection	Unit Sales	RETAIL SALES $		Collection	Unit Sales	RETAIL SALES $
5	Class 5406 MOVE UNO -MS		4	3	5912		Class 5406 MOVE UNO -MS	5	9990		Class 5406 MOVE UNO -MS	5	8142
6	Class 5407 BABY MOVE - MS		-	1	1751		Class 5407 BABY MOVE - MS	6	14216		Class 5407 BABY MOVE - MS	10	26103
7	Class 5406 MOVE UNO -MS			0	0
8	Class 5406 MOVE UNO -MS	4		4	8880
9	Class 5407 BABY MOVE - MS	5		1	2400
10	Class 5406 MOVE UNO -MS			3	3192
11	Class 5406 MOVE UNO -MS		1	2	2230
12	Class 5407 BABY MOVE - MS		-	0	0
13	Class 5406 MOVE UNO -MS			1	1120
14	Class 5407 BABY MOVE - MS		-	0	0
15	Class 5406 MOVE UNO -MS	2		1	1110
16	Class 5406 MOVE UNO -MS	3		0	0
17	Class 5407 BABY MOVE - MS		2	10	26103
18	Class 5407 BABY MOVE - MS	6		3	7696
19	Class 5407 BABY MOVE - MS	7		2	4120
Sheet15

Cell Formulas
Range	Formula
H5	=SUMIFS(D$2:D$19,A$2:A$19,G5,B$2:B$19,">0",B$2:B$19,"<=10")
I5	=SUMIFS(E$2:E$19,A$2:A$19,G5,B$2:B$19,">0",B$2:B$19,"<=10")
L5	=SUMIFS(D$2:D$19,A$2:A$19,K5,C$2:C$19,">0",C$2:C$19,"<=10")
M5	=SUMIFS(E$2:E$19,A$2:A$19,K5,C$2:C$19,">0",C$2:C$19,"<=10")

Formulae copied down for each column.

 

[Aladin Akyurek]

Thanks but I need a formula!

Can anyone help with this?

At a closer look, what do you mean by Top 10? Care to answer this by looking at the following data?

a,10
b,12
c,4
d,3
e,8

What would be Top 3 for the foregoing data?