Sumproduct with Named Range

**clng** · 11-01-2010, 09:55 PM

Hi all,

Been trying to resolve a "hard-coded" range by using Named Range but has been returning "#VALUE!"

By using the below formula; where range J14 to J2608 is hard-coded, it will work.

=SUMPRODUCT(($E$14:$E$2608="Pipeline")*($H$14:$H$2608=B2)*($J$14:$J$2608))

But by changing range J14 to J2608 into a Named Range (see below); the whole thing fails.

=SUMPRODUCT(($E$14:$E$2608="Pipeline")*($H$14:$H$2608=B2)*(PlanRev))

Please help!

Cheers,
CL

**teylyn** · 11-01-2010, 10:34 PM

the Value error means that the ranges are not the same size. Check the definition of PlanRev.

See here for a discussion of Sumproduct error messages http://spreadsheet-toolbox.com/libra...error-message/

**clng** · 11-01-2010, 10:40 PM

Dear teylyn,

Cool. It works. Did't notice that the range are different and also my Named Range includes the Header... (bad choice of range)

Thanks again.
CL