CODEWITHSUNDEEP
excel
Jemz42
Jemz42

Reputation: 7

How to sum for 2 criteria over multiple values in Excel

I have two categories: City and Product. I have sales for each of these over multiple weeks (10 weeks) and want to sum these for a specific city AND product.

I have tried using SUMIFS but this only works for one column of data and not for multiple weeks.

Any help will be greatly appreciated.

Cheers

For example:

I want to know sum of Melbourne and Dairy using one formula (so this should be 61 all up).

Category 1.   Category 2.  Week1     Week2     Week3
Melbourne     Dairy          10        20        5
Sydney        Fruit.         12.       6.        10
Brisbane      Fruit.         4.        8.        9
Melbourne     Fruit.         8.        7.        3
Melbourne     Dairy.         6.        9.        11

Upvotes: 0

Views: 44

Answers (2)

user4039065
user4039065

Reputation:

A simple sumproduct should suffice.

=SUMPRODUCT((A$2:A$5=G2)*(B$2:B$5=H2)*C$2:D$5)

enter image description here

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166790

Something like this:

enter image description here

Array formula, so enter using Ctrl+Shift+Enter

Upvotes: 1

Related Questions