CODEWITHSUNDEEP
excelexcel-formula
Andrei Ion
Andrei Ion

Reputation: 1827

sum if with some conditions

Guys here's what I want to do... I have 3 columns: category, product and quantity. A product can be in more categories... what I want to do is to sum the quantities for each product in each category... I can do SUMIF looking for the product as a criteria but then it will count it no matter the category... do you have any solution? (I don't want to use a pivot because then it will be simple...) To give you an example let's say we have 3 categories... t-shirts, pants and shoes and 3 brands... let's say one is nike and we have it in all three categories... I want to sum the quantities of nike pants ... the quanties of nike t-shirts and the quantities of nike shoes... if I do a SUMIF with nike as a criteria it will sum everything no matter what they are. Thanks!

Here's an example: Let's say we have the following table enter image description here I want the following result enter image description here

Upvotes: 1

Views: 194

Answers (2)

brettdj
brettdj

Reputation: 55692

A basic PivotTable will produce this summary for you

I suggest you see Debra Dalgleigh's site here for the background info - it won't take long to create

Pivot Fields for your output

Upvotes: 2

Jesse
Jesse

Reputation: 1935

Depends on what you want to be simple. Like Brett said, a pivot table is the easiest to implement.

Another solution is to use SUMPRODUCT. The below would find the matches for Category A Product 1.

=SUMPRODUCT(--(A2:A11="A"),--(B2:B11=1),C2:C11)

SUMPRODUCT is well outside of easy I'd say.

Upvotes: 3

Related Questions