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

[XcelLearner]

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.

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??

 

[jasonb75]

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]

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.

 

[RoryA]

It's because COLUMN returns an array, even if you only pass one cell to it.

 

[XcelLearner]

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]

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]

Thanks for your response, jasonb75. However, I think SUM()*SUM() works differently with SUMPRODUCT()??

It does, that was lack of sleep brainfart
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.