Defined names and SUMPRODUCT

[Frank Kabel]

Hi
I would assume you have defined this name as
Sheet1!A$4$1000
and not
Sheet1!A$4:A$1000

Try defining a name for each column and test again.
Note: in your posted formula the brackets are not correct.

-----Original Message-----
Hey,

I am using a sumproduct formula (see below) that Frank

Kabel suggested to pull data from another page.

=SUMPRODUCT(--((Sheet1!A$4:A$1000)=$C1),(Sheet1! E$4:$E$1000)) Works fine.

If I change the formula to use defined names it doesnt

seem to work... am I doing something wrong or is this a
limitation of this formula?

=SUMPRODUCT(--(DefinedRange)=$C1),(DefinedRange)) I get #VALUE errors using this one.

Column C is the "lookup" data.

000 has been defined as "DefinedRange".