Problem with SUMPRODUCT and OFFSET. What is going on?

[TomCon]

The gist of the problem is that i have a simple formula in a cell, =42-ROW(). If one of the arguments in OFFSET references that cell, it works fine and produces the correct result. But, if i include that text, "42-ROW()" as the argument in OFFSET, it does not work.

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!

 

[Habtest]

Please try ROWS() instead of ROW():

Book1
	R	S	T
35	1	2
36	2	3
37	3	4
38
39
40
41
42			4
43			11
44			20
Sheet2

Cell Formulas
Range	Formula
T42:T44	T42	=SUMPRODUCT($R$35:R35,OFFSET($S$37,1-ROWS($R$35:R35),0,ROWS($S$35:S35),1))

 

[TomCon]

Yes, that does work, so thank you, allows me to have the formula "in one cell" instead of breaking it up over multiple columns! Thanks! Still seems odd/mysterious as to why the same formula that works when referenced in a cell does not work as an arugment...but se la vie i guess. thanks!

 

[Habtest]

Yes, that does work, so thank you, allows me to have the formula "in one cell" instead of breaking it up over multiple columns! Thanks! Still seems odd/mysterious as to why the same formula that works when referenced in a cell does not work as an arugment...but se la vie i guess. thanks!

No problem, thanks for the feedback.

ROW in SUMPRODUCT formula returns the row number in array form instead of numeric somehow. To work around it you may use ROWS, or wrap sum around as SUM(ROW()).

Book1
	R	S	T	U
35	1	2
36	2	3
37	3	4
38
39
40
41
42			4	4
43			11	11
44			20	20
Sheet2

Cell Formulas
Range	Formula
T42:T44	T42	=SUMPRODUCT($R$35:R35,OFFSET($S$37,1-ROWS($R$35:R35),0,ROWS($S$35:S35),1))
U42:U44	U42	=SUMPRODUCT($R$35:R35,OFFSET($S$37,42-SUM(ROW()),0,SUM(ROW())-41,1))