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?

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?

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?

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

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.

"leolin" wrote:

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?

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

--
Regards,

Peo Sjoblom


"leolin" wrote in message
...
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?

thank you, this solved the problem! :)

"Dave Peterson" wrote:

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