Prev: Splitting multiple cell contents from 1st column into 4 columns
Next: how to combine several files, all with same columns, into one
From: Amethyst on 28 Apr 2010 17:38
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?
--
Amethyst
From: Jim Thomlinson on 28 Apr 2010 17:54
Try this...

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

--
HTH...

Jim Thomlinson


"Amethyst" wrote:

> 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?
> --
> Amethyst
From: Joe User on 28 Apr 2010 19:31
"Amethyst" wrote:
> 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 -----

"Amethyst" wrote:

> 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?
> --
> Amethyst
 | 
Pages: 1
Prev: Splitting multiple cell contents from 1st column into 4 columns
Next: how to combine several files, all with same columns, into one