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.


[TABLE="width: 1263"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]COLLECTION [/TD]
[TD]TOP 10[/TD]
[TD]BOTTOM 10[/TD]
[TD]UNIT SALES [/TD]
[TD]RETAIL SALES $ [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5406 MOVE UNO -MS[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]7950[/TD]
[TD][/TD]
[TD="align: left"]TOP 10 ONLY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]BOTTOM 10 ONLY[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5406 MOVE UNO -MS[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5407 BABY MOVE - MS[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3380[/TD]
[TD][/TD]
[TD="align: left"]Collection[/TD]
[TD="align: left"]Unit Sales[/TD]
[TD="align: left"]RETAIL SALES $ [/TD]
[TD][/TD]
[TD="align: left"]Collection[/TD]
[TD="align: left"]Unit Sales[/TD]
[TD="align: left"]RETAIL SALES $ [/TD]
[/TR]
[TR]
[TD]Class 5406 MOVE UNO -MS[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]5912[/TD]
[TD][/TD]
[TD="align: left"]Class 5406 MOVE UNO -MS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: left"]Class 5406 MOVE UNO -MS[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Class 5407 BABY MOVE - MS[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]1751[/TD]
[TD][/TD]
[TD="align: left"]Class 5407 BABY MOVE - MS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: left"]Class 5407 BABY MOVE - MS[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Class 5406 MOVE UNO -MS[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5406 MOVE UNO -MS[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]8880[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5407 BABY MOVE - MS[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5406 MOVE UNO -MS[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3192[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5406 MOVE UNO -MS[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2230[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5407 BABY MOVE - MS[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5406 MOVE UNO -MS[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5407 BABY MOVE - MS[/TD]
[TD][/TD]
[TD]-[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5406 MOVE UNO -MS[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1110[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5406 MOVE UNO -MS[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5407 BABY MOVE - MS[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]26103[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5407 BABY MOVE - MS[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]7696[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Class 5407 BABY MOVE - MS[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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