SUMPRODUCT(ARRAY) RETURNING "VALUE#"

[Amethyst]

I'm using
=SUMPRODUCT(--('sheetname'!$c$2:$c$100="SAname"),--('sheetname'!$h$2:$h$100))
to find an SA in Col C and add up his roll call in Col H. The formula worked
in the original worksheet, but when additional lines were added to
'sheetname', the formula is returning #VALUE!. The formula looks OK. What
went wrong?

 

[Jim Thomlinson]

Try this...

=SUMPRODUCT(('sheetname'!$c$2:$c$100="SAname")*('sheetname'!$h$2:$h$100))

 

[Joe User]

I'm using
=SUMPRODUCT(--('sheetname'!$c$2:$c$100="SAname"),
--('sheetname'!$h$2:$h$100)) [....]
the formula is returning #VALUE!.

Try:

=SUMPRODUCT(--('sheetname'!$c$2:$c$100="SAname"),
'sheetname'!$h$2:$h$100)

The root cause of the problem is probably non-numeric values in H2:H100,
e.g. null strings ("").

Most non-numeric values [1] cause #VALUE errors in arithmetic expression
such as --('sheetname'!$h$2:$h$100) and
('sheetname'!$c$2:$c$100="SAname")*('sheetname'!$h$2:$h$100)).

But SUMPRODUCT treats non-numeric values in array arguments as zero.

PS: Alternatively, you can use:

=SUMIF('sheetname'!$c$2:$c$100, "SAname",
'sheetname'!$h$2:$h$100)


-----
Endnotes:

[1] Text that look likes a number is treated as a number in arithmetic
expressions. Presumably that does not apply in this case since, then, you
would not see a #VALUE error.


----- original message -----