# Excel: Use array to sum product by row

Is there a way to use `SUMPRODUCT` or use an array formula with arrays of differing sizes?

Currently I have a list of ~1500 products in column `A` and columns `B`-`I` contain their respective component needs like so:

`````` A        B      C      D      E      F      G      H      I      J
¢20   ¢55     ¢75    ¢61    ¢12    ¢30    ¢46    ¢83     total
wdgt     9      1      1      0      0      0      0      0        235
fzzy     0      0      10     9      3      1      1      0       1411
sqr      10     10     10     10     10     10     10     10      3820
llc      2      4      6      8      10     12     14     16      3650
dbl      2      4      8      16     32     64     128    256    31276
....
``````

Each of the respective components has a set value , and `J` is currently calculated by

``````SUMPRODUCT(B2:I2,\$B1:\$I1)...
``````

I would like to have `J` be an array formula like

``````{=SUMPRODUCT(B2:I6,\$B1:\$I1)}
``````

but doing so gives #VALUE! error and:

``````{=SUM(B2:I6,\$B1:\$I1)}
``````

for each row i.e. the sum of the total column, 40392.

Is there a way to do this with an array formula?

• Please define "strange results" and "strange behavior". We don't know what that is. – CharlieRB Aug 25 '14 at 13:25
• Also, your formula is multiplying by the text (prt1, prt2, prt3, etc.) in the first row. That won't work. Was that your intent? – CharlieRB Aug 25 '14 at 13:34
• For that matter, please define "correct behavior"; i.e., what do you want it to do? Giving some examples of the results you want/expect would help us to help you get them. Also, why are you making the columns `B` and `I` absolute, but not the row numbers? And, just for good measure, what version of Excel are you using? – G-Man Aug 25 '14 at 22:55
• Why would you want to SUM if you want an array? – LDC3 Aug 26 '14 at 0:51

## 1 Answer

Via trial and error

``````{=SUM(B2:I6,\$B1:\$I1)}
``````

returns an error #VALUE!

``````{=SUM(B2:I6,\$B1:\$I1)}
``````

returns the sum of the totals

``````{=\$B\$1:\$I\$1*B2:I6}
``````

returns 180, 0, 200, 40, 40 (which I cannot explain). This was what I thought was strange.

``````{=\$B\$1*B2:B6+\$C\$1*C2:C6+…+\$H\$1*H2:H6+\$I\$1*I2:I6}
``````

returns the correct totals for each row.

Alternatively, I previously had something like:

``````=SUMPRODUCT(\$B\$1:\$I\$1,B2:I2)
``````

in each row, but for 1500+ rows, the evaluation was getting longer and longer as the list is constantly changing and increasing.