My issue is that i want to fill down the SUMPRODUCT formula. I want one of its arguments to fill down in the normal way, say you fill down $R$35:R35 and it gets one cell larger with each fill down. But, the other argument i want to go in the opposite direction, starting at the bottom of a range and going up by one cell with each filled down row. So, for instance 3 rows of SUMPRODUCT would look like this (the need to go "up" in the second array is why i want to use OFFSET).

=SUMPRODUCT($R$35:R35,S37:S37)

=SUMPRODUCT($R$35:R36,S36:S37)

=SUMPRODUCT($R$35:R37,S35:S37)

etc.

If you want to follow this example, fill some numbers in R35:R37 and S35:S37 that will work with SUMPRODUCT, any numbers will do.

If in R42 i have this formula: =42-ROW()

And in S42 i have this formula: =ROW()-41

And in T42 i have this formula: =SUMPRODUCT($R$35:R35,OFFSET($S$37,R42,0,S42,1))

It works fine in row 42, and it works fine when i fill down R42, S42, and T42.

But if i substitute either of the formulas in R42 or S42, into the OFFSET, it does not work, and gives either the value 0 in one case, and #VALUE! in most cases.

Try this in T42 and fill down: =SUMPRODUCT($R$35:R35,OFFSET($S$37,42-ROW(),0,S42,1))

T42 gets 0 and T43 and T44 get #VALUE!

Can anybody explain what is going on here? I guess i have my own workaround, just make more columns, break up the formula, and clutter the sheet.

Is there a way to get that SUMPRODUCT(OFFSET...)) formula to work without breaking it up in this way. Is this an Excel BUG?

Thanks!