Problem with blank cells affecting formula

**Gooford** · 09-30-2009, 06:10 AM

Hi,

=(SUMPRODUCT((D6:D300="a")*(F6:F300="MATT")*(J6:J300)))

Count all A's that are also Matt and add up the corresponding figs in J

If use the formula above to 179 (where there are values in the cells) then I get the answer 170 which is correct.

Currently with the it set to review all cells from 6 to 300 which includes blanks then it returns #value therfore I assume it is not coping with the blank cells.

How do I get around this?

Thanks in advance

**teylyn** · 09-30-2009, 06:17 AM

can you post a data sample?

**DonkeyOte** · 09-30-2009, 06:18 AM

Sounds like your blanks in J are actually nulls so possibly revert to double unary:

=SUMPRODUCT(--(D6:D300="a"),--(F6:F300="MATT"),J6:J300)

Using the * method you are explicitly coercing the values in J to numbers, if J blanks are Nulls (0 length text string) then this will generate #VALUE! whereas the alternative approach will ignore non-numerics in the final range.