sumproduct

[Carole Kaufman]

Hi,

This looks sooo right to me, but gets an error #VALUE! Not sure where
it's wrong. Any help would have me a lot more hours staring at the
screen:

=SUMPRODUCT(DUMP!A:A=A13)*(DUMP!B:B=$A$1)*(DUMP!E:E) ==> #VALUE!

I have tried re-writing the formula as:

=SUM(IF((DUMP!A:A=A13)+(DUMP!B:B=$a$1),1,0)*DUMP!E:E)==> 0 (but should
be a value greater than 0)

The value A13 exists in Dump, column A, more than once.
The value A1 exists in Dump, column B, more than once.
Numerical data exists in Dump, column E.

What am I doing wrong??

 

[Anon]

Hi,

This looks sooo right to me, but gets an error #VALUE! Not sure where
it's wrong. Any help would have me a lot more hours staring at the
screen:

=SUMPRODUCT(DUMP!A:A=A13)*(DUMP!B:B=$A$1)*(DUMP!E:E) ==> #VALUE!

I have tried re-writing the formula as:

=SUM(IF((DUMP!A:A=A13)+(DUMP!B:B=$a$1),1,0)*DUMP!E:E)==> 0 (but should
be a value greater than 0)

The value A13 exists in Dump, column A, more than once.
The value A1 exists in Dump, column B, more than once.
Numerical data exists in Dump, column E.

What am I doing wrong??

For SUMPRODUCT you cannot use whole column references, like A:A. You must
use A1:A1000, or whatever range is appropriate.

For SUM(IF(..., you need to array-enter this, using CTRL+SHIFT+ENTER, not
just ENTER.