SUMPRODUCT(SUMIFS) How do I use them?

[boon-yao.tek]

I would like to sumifs for multiple criteria on a single range. I have seen someone using sumproduct(with sumifs).
However I couldn't get it to work properly.

I have attached a sample file to illustrate my problem.
sumproductsumifs.xlsx
The actual sum that I should be getting is 36962 but my formula only returns me 20908.

Please help to solve is it something wrong with how I construct the formula, or do you know of better ways to get the final result (pivot is not a choice for my problem).


Thanks,
Ray

[Ace_XL]

Try

=SUMPRODUCT($F$9:$F$31,(C9:C31=E33)*((D9:D31=D35)+(D9:D31=D36))*((E9:E31=D38)+(E9:E31=D39)))

[XOR LX]

Hi,

=SUMPRODUCT((C9:C31=E33)*((D9:D31=D35)+(D9:D31=D36))*((E9:E31=D38)+(E9:E31=D39))*$F$9:$F$31)

Regards

[XOR LX]

By the way. the issue you were having was the way in which you were trying to combine SUMIFS and SUMPRODUCT. Effectively, the part:

D9:D31,D35:D36

for example, you intended to mean "where the value in D9:D31 is either of the values in D35 and D36"

but Excel could not interpret this in your set-up. (The use of the + sign within the SUMPRODUCT solution effectively acts as an OR operator.)

Not sure if there is a similar way to coerce SUMIFS with Boolean logic.

Regards

[boon-yao.tek]

First of all, thanks Ace and XOR for showing how to make use of the plus signs.

But I have another issue with using the + sign.

So sorry, that I did not provide a good example in the sample.
In my actual report, I have to allow other users to select sometimes just 1 product type or sometimes up to 10 product types.

I had previously put them into a dynamic named range(using the offset so if more product types are selected, the name range will automatically include more).
I don't see how the plus sign can work for this situation.

I'm totally fine too to use other formula than sumproduct in this case.

[boon-yao.tek]

Let me know if u want me to provide a better sample report that illustrate my problem.

[XOR LX]

That's fine. You can use:

=SUMPRODUCT((C9:C31=E33)*(ISNUMBER(MATCH(D9:D31,D35:D36,0)))*(ISNUMBER(MATCH(E9:E31,D38:D39,0)))*$F$9:$F$31)

Regards

[boon-yao.tek]

Gosh you guys are really genius!

Thanks so much, I should have come to you guys earlier. I have spent the whole morning just to figure this out.