sumproduct returning #NA

L

leolin

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?
 
P

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))
 
D

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).
 
B

bj

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.
 
P

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
 
L

leolin

thank you, this solved the problem! :)

Dave Peterson said:
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).
Click to expand...
 
Top