SumProduct Error?

I am trying to use the SUMPRODUCT function but can't
understand why I am getting the #N/A error? This is the
syntax that I am using:

=SUMPRODUCT((NAICS_Industry="Electric Services")*
(Auditors="PWC"))

NAICS_Industry and Auditors are named ranges. They are
both valid because I can do a COUNTIF using both ranges
and it returns the correct value.

What makes this more frustrating is that if I replace the
NAICS_Industry range with a range called SIC_Industry, it
works.

Does anyone have a clue why this would do this?

Hi John

ranges in SUMPRODUCT have to be of equal size - so if you go into insert /
name / define and do your two ranges have the same number of rows?

additionally,
=SUMPRODUCT(--(NAICS_Industry="Electric Services"),--(Auditors="PWC"))

might be a more efficient way of writing the function - check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
for more details

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"john" <[email protected]> wrote in message
news:[email protected]...
>I am trying to use the SUMPRODUCT function but can't
> understand why I am getting the #N/A error? This is the
> syntax that I am using:
>
> =SUMPRODUCT((NAICS_Industry="Electric Services")*
> (Auditors="PWC"))
>
> NAICS_Industry and Auditors are named ranges. They are
> both valid because I can do a COUNTIF using both ranges
> and it returns the correct value.
>
> What makes this more frustrating is that if I replace the
> NAICS_Industry range with a range called SIC_Industry, it
> works.
>
> Does anyone have a clue why this would do this?
>
>

With Reference to JulieD's comment, Given that the ranges are the same
number of rows (but for me, not having the same number of rows gives a
#Value error), does the NAICS_Industry range contain a cell that has an #N\A
error in it (perhaps it is produced with a lookup formula). If so, there's
your huckleberry. In most cases, Errors in the source range get transmitted
to the function and dominate the output.

If that is the case, You could try
=SUMPRODUCT(If(IsError(NAICS_Industry="Electric
Services"),0,(NAICS_Industry="Electric Services))*
(Auditors="PWC"))

--
Regards,
Tom Ogilvy

"john" <[email protected]> wrote in message
news:[email protected]...
> I am trying to use the SUMPRODUCT function but can't
> understand why I am getting the #N/A error? This is the
> syntax that I am using:
>
> =SUMPRODUCT((NAICS_Industry="Electric Services")*
> (Auditors="PWC"))
>
> NAICS_Industry and Auditors are named ranges. They are
> both valid because I can do a COUNTIF using both ranges
> and it returns the correct value.
>
> What makes this more frustrating is that if I replace the
> NAICS_Industry range with a range called SIC_Industry, it
> works.
>
> Does anyone have a clue why this would do this?
>
>