Problem with SUMPRODUCT and OFFSET. What is going on?

TomCon

Active Member
Joined
Mar 31, 2011
Messages
288
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
Joined
Jul 30, 2020
Messages
161
Office Version
  1. 365
Platform
  1. Windows
Please try ROWS() instead of ROW():

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
Joined
Mar 31, 2011
Messages
288
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
Joined
Jul 30, 2020
Messages
161
Office Version
  1. 365
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,135
Messages
5,640,321
Members
417,137
Latest member
Dashivas123

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top