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


leolin wrote:

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

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom


"leolin" wrote in message
...
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:J4 00="C"))

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



--

Dave Peterson