SUMPRODUCT with calculated text strings returning Zero

[jbwhite99]

A B C D
10 ABC Text 1 100
11 123 Text 1 120
12 ABC123 Text 1 140
13 XYZ Text 1 160
14 555 Text 1 222
15 444 Text 1 444
16 SP 10 0 0

Consider the above table - when you see a line with "SP" in column A,
want to get the sum of column D weighted by column C, but only betwee
the line above SP (15 in this case) and the value in cell B16 (10 i
this case). In effect, this is =SUMPRODUCT(C10:C15,D1015). I
order to do it with formulae (this could be repeated randomly down th
page and across the page), I have built the following
SUMPRODUCT("$C"&$B$16&":$C"&ROW()-1,"$"&CHAR(COLUMN()+64)&$B$16&":$"&CHAR(COLUMN()+64)&ROW()-1)

This returns =SUMPRODUCT("$C10:$C15","$D10:$D15") which returns zero
How do I get Excel to give me the SUMPRODUCT of the value of th
strings? INDIRECT didn't like me. Note that this formula repeat
itself both left to right and up to down so I'd rather leave i
somewhat in formulae if possible.

Thanks, Brando

 

[Frank Kabel]

Hi
one way:
=SUMPRODUCT(INDIRECT("C" & $B$16 & ":C" & ROW()-1),INDIRECT("D" & $B$16
& "" & ROW()-1))

 

[jbwhite99]

Frank,

Thanks for the suggestion, but I still got (in Office XP) a #VALU
error.

The net is how do you get a text string as input to SUMPRODUCT?
"$C10:$C15" should be a valid value, but I don't see how to ge
SUMPRODUCT to get it to accept.

Thanks,
Brando

 

[Frank Kabel]

Hi
if you have created a string which should be a cell reference use
INDIRECT. e.g.
=INDIRECT("$C10:$C15")