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

A

AnaLib

New Member
Joined
Feb 1, 2018
Messages
3
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]
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Hi,

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


Book1
ABCDEFGHIJKLM
1COLLECTIONTOP 10BOTTOM 10UNIT SALESRETAIL SALES $
2Class 5406 MOVE UNO -MS47950TOP 10 ONLYBOTTOM 10 ONLY
3Class 5406 MOVE UNO -MS100
4Class 5407 BABY MOVE - MS13380CollectionUnit SalesRETAIL SALES $CollectionUnit SalesRETAIL SALES $
5Class 5406 MOVE UNO -MS435912Class 5406 MOVE UNO -MS59990Class 5406 MOVE UNO -MS58142
6Class 5407 BABY MOVE - MS-11751Class 5407 BABY MOVE - MS614216Class 5407 BABY MOVE - MS1026103
7Class 5406 MOVE UNO -MS00
8Class 5406 MOVE UNO -MS448880
9Class 5407 BABY MOVE - MS512400
10Class 5406 MOVE UNO -MS33192
11Class 5406 MOVE UNO -MS122230
12Class 5407 BABY MOVE - MS-00
13Class 5406 MOVE UNO -MS11120
14Class 5407 BABY MOVE - MS-00
15Class 5406 MOVE UNO -MS211110
16Class 5406 MOVE UNO -MS300
17Class 5407 BABY MOVE - MS21026103
18Class 5407 BABY MOVE - MS637696
19Class 5407 BABY MOVE - MS724120
Sheet15
Cell Formulas
RangeFormula
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
sum table based on rows and multiple years columns
Replies
2
Views
511
vampsthevampyre
V
M
  • Question Question
Need to get Sum of a Column based on values satisfying criteria in 6 different columns
Replies
0
Views
687
mrugeshpoojary
M
A
  • Question Question
copy data from multiple sheets to one sheet based on dropdown and summing values
Replies
2
Views
364
Alaa mg
A
G
  • Question Question
Sum Unique Value based on different Time criterias
Replies
5
Views
1K
sandy666
sandy666
L
  • Question Question
Rank With Multiple Criteria
2
Replies
10
Views
1K
lrobbo314
lrobbo314

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top