# SUMPRODUCT IFS?

### Question

• Trying to figure out a SUMPRODUCT IFS formula for the following.

Would like to SUMPRODUCT Number Widgets x Gross Price.

Text criteria are both in column D, sum range is column E.

Tried this, but it returns zero:

=SUMPRODUCT(--(D39:D61="Number Widgets"),--(D39:D61="Gross Price"),E39:E61)

Any assistance would be greatly appreciated. Thanks.

COLUMN D                        COLUMN E

 Number Widgets 4,031 Net Widget Price 171.36 Tax 10% 17.14 Fees 11.40 Gross Price 199.90 Number Widgets 4,016 Net Widget Price 135.55 Tax 10% 13.55 Fees 10.80 Gross Price 159.90 Number Widgets 1,427 Net Widget Price 81.00 Tax 10% 8.10 Fees 10.80 Gross Price 99.90

Monday, May 12, 2014 10:22 PM

• Assuming that Gross Price will always be 4 cells below Number Widgets:

=SUMPRODUCT(--(D39:D61="Number Widgets"),E39:E61,E43:E65)

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Tuesday, May 13, 2014 4:20 PM
Tuesday, May 13, 2014 5:31 AM

### All replies

• Assuming that Gross Price will always be 4 cells below Number Widgets:

=SUMPRODUCT(--(D39:D61="Number Widgets"),E39:E61,E43:E65)

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Tuesday, May 13, 2014 4:20 PM
Tuesday, May 13, 2014 5:31 AM
• As usual Hans, that works perfectly.

I don't *completely* understand why, but thanks ..!

Tuesday, May 13, 2014 4:21 PM
• You want to multiply E39 with E43, E40 with E44, etc., and sum the products

That would be =SUMPRODUCT(E39:E61,E43:E65)

To sum only if column D contains "Number Widgets", we add --(D39:D61="Number Widgets")

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Tuesday, May 13, 2014 4:38 PM