Using cell references in sumproduct formula

  • Thread starter Thread starter Kim Locklin
  • Start date Start date
K

Kim Locklin

Hi -

I am trying to use cell references in a formula instead of hard numbers.

Here¹s the formula:
=SUMPRODUCT(($C$3>{0;1200000;1600000;2000000})*($C$3-{0;1200000;1600000;2000
000})*{0.05;0.01;0.01;0.03})

Instead of ³1200000² I want to use ³$c$1²
I keep getting an invalid formula message. I have tried removing the $,
adding ( ) to the cell reference ­ to no luck.

Any suggestions?

Thank you!

Kim
 
Kim Locklin said:
Hi -

I am trying to use cell references in a formula instead of hard numbers.

Here¹s the formula:
=SUMPRODUCT(($C$3>{0;1200000;1600000;2000000})*($C$3-{0;1200000;1600000;2000
000})*{0.05;0.01;0.01;0.03})

Instead of ³1200000² I want to use ³$c$1²
I keep getting an invalid formula message. I have tried removing the $,
adding ( ) to the cell reference ­ to no luck.

Any suggestions?

Thank you!

Kim
Click to expand...

It's not the SUMPRODUCT that's your problem, it's the array constants (e.g.
{0;1200000;1600000;2000000} ).
An array constant can only contain constants, not cell references. See Help:
"Items that an array constant can contain".
 
Back
Top