ExcelBanter - sumproduct returning #NA

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - sumproduct returning #NA (https://www.excelbanter.com/excel-discussion-misc-queries/23733-sumproduct-returning-na.html)

leolin

sumproduct returning #NA

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?

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

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

leolin

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

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?

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

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

leolin

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

All times are GMT +1. The time now is 08:05 AM.