SUM of arrays works, but SUMPRODUCT of the same arrays do not

XcelLearner

New Member
Joined
Feb 6, 2016
Messages
38
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,
I have a data set, in which two arrays (in time series) are power generated (C column), and the gas price (M column). I am taking the weighted gas price of the power plant, assumingly by taking SUMPRODUCT/SUM of the arrays (power generated, and gas price). The arrays are created by OFFSET functions.

The issue I am encountering is that although there is no error for each array, SUMPRODUCT gives 0 as an answer.

DgQrjwh.png


In more details, for testing purpose in Q4 2019:
- SUM of Q4 2019 power generated gives 1264, the same as result from the formula: =SUM(OFFSET('Monthly Production'!$C$21,(COLUMN(BF1)-COLUMN($AU$1))*3,0,3,1))
- SUM of Q4 2019 gas price gives 18.07, the same as from the formula: =SUM(OFFSET('Monthly Production'!$M$21,(COLUMN(BF1)-COLUMN($AU$1))*3,0,3,1))

That means that the arrays work as intended.
However, when I SUMPRODUCT of the two arrays, using formula
=SUMPRODUCT(OFFSET('Monthly Production'!$C$21,(COLUMN(BF1)-COLUMN($AU$1))*3,0,3,1),
OFFSET('Monthly Production'!$M$21,(COLUMN(BF1)-COLUMN($AU$1))*3,0,3,1))
It gives me an answer of zero.

Where did I go wrong, or maybe SUMPPODUCT works in a specific way please??
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
Sumproduct creates an unwanted array in the second part of offset which results in a #VALUE! error in that part of the formula. It is easier to simply use =SUM(...)*SUM(...) for what you need.
There are a few ways to work around the problem but they are going to unnecessarily over complicate the formula.
 

XcelLearner

New Member
Joined
Feb 6, 2016
Messages
38
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for your response, jasonb75. However, I think SUM()*SUM() works differently with SUMPRODUCT()??

I talked to a colleague about this problem, and she suggested that I should add two dashes (--) in the SUMPRODUCT before the OFFSET function.

My formula will look like this:
=SUMPRODUCT(--(OFFSET('Monthly Production'!$C$21,(COLUMN(BF1)-COLUMN($AU$1))*3,0,3,1)),
--(OFFSET('Monthly Production'!$M$21,(COLUMN(BF1)-COLUMN($AU$1))*3,0,3,1)))

I did, and it works like a charm. She couldn't make sense to me very well why that two dashes (--) help. She said that because in my OFFSET I have a multiple of COLUMN()*3, so Excel doesn't understand.
I've been using SUMPRODUCT(OFFSET(),OFFSET()) perfectly, but those formulas don't include multiple *3 like in this one. This issue still baffles me. I would greatly appreciate if someone can help me understand this.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It's because COLUMN returns an array, even if you only pass one cell to it.
 

XcelLearner

New Member
Joined
Feb 6, 2016
Messages
38
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

It's because COLUMN returns an array, even if you only pass one cell to it.
I think that (COLUMN()-COLUMN())*3 returns a value, such as (10-2)*3. Is that what you mean?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, it actually returns the result as an array - if you use the formula evaluate tool, you should see a result like {33}, rather than just 33, being returned. That is what confuses SUMPRODUCT (even though it only happens as part of a SP formula).
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
Thanks for your response, jasonb75. However, I think SUM()*SUM() works differently with SUMPRODUCT()??
It does, that was lack of sleep brainfart :oops:
What I meant was =SUM(OFFSET(...)*OFFSET(...)) but if I had taken the time to wake up and think about it first I would have noticed that it will return an error.
I wasn't aware that using --OFFSET would cancel out the problem, but then again I so try to avoid the use of offset whenever possible.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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