sumproduct returning #NA

[Guest]

this formula works
=SUMPRODUCT((AQ8:AQ400="A")*(AR8:AR400="B"))

however when i try the following formula, it returns #N/A please let me know
why, appreciate your help, thanks!
=SUMPRODUCT((AQ8:AQ400="A")*(AR8:AR400="B")*(J8:J400="C"))

ps. Column J is a column of VLOOKUP forumlas, would that cause the error?

 

[Peo Sjoblom]

The only way that formula can return #N/A is that J8:J400 has an NA error
itself
so in your vlookup add something like

=IF(ISNUMBER(MATCH(lookup_value,A2:A20,0)),VLOOKUP(lookup_value,A2:B20,2,FALSE),"")

or the more costly way

=IF(ISNA(VLOOKUP(Lookup_value,A2:B20,2,FALSE)),"",VLOOKUP(Lookup_value,A2:B20,2,FALSE))

 

[Guest]

thanks for the quick post but my column J does not contain any NA errors....

 

[Dave Peterson]

I'd look again.

Select J8:J400
First check for constant errors:
edit|goto|special|Constants (and uncheck all options except Errors)
Then check for formula errors:
edit|goto|special|Formulas (and uncheck all options except Errors)

Maybe the errors are hidden by formatting (or conditional formatting).

 

[Guest]

What do you get when you do
=SUMPRODUCT((J8:J400="C"))
by itself.
If you get #N/A
try some smaller subsets to try to locate the problem area.

 

[Peo Sjoblom]

If not then the formula can't be using same sized ranges because you can get
that kind of error if the ranges are of different size or if a range holds
the error itself

 

[Guest]

thank you, this solved the problem!

I'd look again.

Select J8:J400
First check for constant errors:
edit|goto|special|Constants (and uncheck all options except Errors)
Then check for formula errors:
edit|goto|special|Formulas (and uncheck all options except Errors)

Maybe the errors are hidden by formatting (or conditional formatting).