Cell references in sumproduct formulas

[Kim Locklin]

Hi -

I am trying to insert cell references in a sumproduct formula.

Here¹s my 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 typing in ³1200000² I want to use say ³C1²
I keep getting a formula error.

Any suggestions?

Thank you!

Kim

 

[Stephen Dunn]

Hi Kim,

you can only use constant values within an constant-array, so {0;C1;1600000}
is out of the question. You could use a range reference in place of the
constant-array:

=SUMPRODUCT(($C$3>$A$1:$A$4)*($C$3-$A$1:$A$4)*{5;1;1;3}/100)

or, if you are just looking for a way to shorten the formula, you could use:

=SUMPRODUCT(($C$3>{0;12;16;20}*10^5)*($C$3-{0;12;16;20}*10^5)*{5;1;1;3}/100)

but you can't combine the two.

Steve D.