# Problem with SUMPRODUCT and OFFSET. What is going on?

#### TomCon

##### Active Member
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!

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Habtest

##### Board Regular

Book1
RST
3512
3623
3734
38
39
40
41
424
4311
4420
Sheet2
Cell Formulas
RangeFormula
T42:T44T42=SUMPRODUCT(\$R\$35:R35,OFFSET(\$S\$37,1-ROWS(\$R\$35:R35),0,ROWS(\$S\$35:S35),1))

#### TomCon

##### Active Member
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

##### Board Regular
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
RSTU
3512
3623
3734
38
39
40
41
4244
431111
442020
Sheet2
Cell Formulas
RangeFormula
T42:T44T42=SUMPRODUCT(\$R\$35:R35,OFFSET(\$S\$37,1-ROWS(\$R\$35:R35),0,ROWS(\$S\$35:S35),1))
U42:U44U42=SUMPRODUCT(\$R\$35:R35,OFFSET(\$S\$37,42-SUM(ROW()),0,SUM(ROW())-41,1))

Replies
3
Views
259
Replies
5
Views
162
Replies
6
Views
69
Replies
9
Views
273
Replies
6
Views
122

1,130,320
Messages
5,641,511
Members
417,213
Latest member
wikk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back