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

#### XcelLearner

##### New Member
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??

### 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
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
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
It's because COLUMN returns an array, even if you only pass one cell to it.

#### XcelLearner

##### New Member

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

Replies
3
Views
85
Replies
8
Views
221
Replies
6
Views
106
Replies
0
Views
84
Replies
1
Views
316

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?

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