How to build a SumProduct with multiple criterias.

**gouleta** · 06-04-2012, 11:17 AM

Hi all

I think this one is a bit complex.

I am trying to track the evolution of an account's call offs from a "reserve" order for a given timeline. The data is available only in the following format:

Horizontal data:
The week during which the account pulled quantities from their reserve orders

Vertical data:
The different accounts
The Product Categories
The month for which an order is requested to ship

My problem here is that my initial idea was to use a SumProduct formula, but it seems to be able to only account for 2 vertical data criterias, while I have three (account, product categories and the month for which an order is requested to ship). I have highlighted in yellow the section within the Data tab that are used as criterias.

Any idea how to solve this? The SumProduct formulas are located on the Account 2 sheet, starting cell E35. The top section is just a series of Sums recapping the data located underneath it.

Thank you

**zbor** · 06-04-2012, 11:36 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**gouleta** · 06-04-2012, 11:41 AM

Thank you zbor. Done. Any idea on how to solve this problem?

**SteveG** · 06-04-2012, 11:42 AM

Hi,

This seemed to work. There may be easier ways.

In E35. =SUMIFS(OFFSET(DATA_2!$O$19,0,MATCH('ACCOUNT 2'!E$32,WEEKNUM,0),COUNTA(DATA_2!$A$20:$A$55),1),DATA_2!$A$20:$A$55,'ACCOUNT 2'!$C$4,DATA_2!$I$20:$I$55,'ACCOUNT 2'!$B35,DATA_2!$N$20:$N$55,'ACCOUNT 2'!$C$32)

HTH
Steve