SUMPRODUCT help?

**leematthews** · 07-22-2011, 11:17 AM

Might be a dumb question, but here goes:

I'm trying to sum the product of the values in Column E and F (units and price) but only if two conditions exist...

If Cell B is Empty (signaling the order is open)
Cell C is the name of the product

Anyway here's a few codes that I've tried:

=SUMPRODUCT(--(B:B=""),--(C:C="AAPL"),F:F*E:E)

It just gives me the #VALUE! ...If I remove the *EE then it gives me just the sum...So Help!

**Colin Legg** · 07-22-2011, 11:23 AM

Do your columns have text headers? If there are non-numerics in columns E or F then your formula will return that error because you are using the * operator.

SUMPRODUCT is an expensive function, so it's really best to minimize the size of the ranges passed into it. Also note (not relevant to you) that you can't use entire column references pre XL 2007.

Here's a revised formula which excludes the header row, and will not error if there are text values in columns E or F.

=SUMPRODUCT(--(B2:B1000=""),--(C2:C1000="AAPL"),F2:F1000,E2:E1000)

**ChemistB** · 07-22-2011, 11:36 AM

You should also be able to just replace the * with a ,
Unless the values in E are recorded as text and need to be coerced into being viewed as numbers.

**leematthews** · 07-22-2011, 11:55 AM

Yep, that pretty much did it. Thanks for the help guys!

**thepdaoson** · 07-22-2011, 11:56 AM

Try this =SUMPRODUCT((B2:B8="")*(C2:C8="APPL")*(E2:E8)*(F2:F8)). Is it correct? If you upload your file and give example of your expected results, then it should be easier.