# Sumproduct help in Excel

1. Jun 12, 2016

### Square1

Hi. I am having issues using the sumproduct function in excel.

I two values in cells A1 and B1 that I want to multiply. I have two values in cells C1 and D1 that I want to multiply, and so on and so forth. I want to add these products afterwards. I think I have a problem with the syntax I use...

2. Jun 12, 2016

### Staff: Mentor

You probably have many unused cells, at least on another sheet. Use them for your products and add them there.

Edit: e.g. table2!A1=table1!A1*table1!B1 and table2!C1=table1!C1*table1!D1 and table1!A100 = sum(table2!A1:table2!A99)

Last edited: Jun 12, 2016
3. Jun 12, 2016

### Square1

Is there really no other way to use the sumproduct function? Does having my data in horizontal form have something to do with it?

4. Jun 12, 2016

### Staff: Mentor

I don't know any. My trials weren't successful. But so what? You can even insert additional columns for the products, sum them in another and then hide the new column. This way you won't see the results in between.

5. Jun 12, 2016

### Square1

Ughhh...sometimes I am amazed how limited excel is some of its utility. No it's not a big deal but come on Microsoft...Thanks for the advice!

6. Jun 12, 2016

### Staff: Mentor

You could write it out but in this case I strongly recommend to use an editor, e.g. textpad, to copy and paste and substitute all the cell names instead of doing it in excel. There seems to be no internal buffers for calculations.

7. Jun 12, 2016

### Staff: Mentor

Eureka! Got it! SUMPRODUCT(A1:A10;B1:B10)

8. Jun 13, 2016

### Staff: Mentor

Which you can find by getting help for "sumproduct" right in Excel. Both arrays have to be the same size and shape.
The syntax is "=sumproduct(<upper left cell A>: <lower right cell A>, <upper left cell B>: <lower right cell B>)"

9. Jun 13, 2016

### Staff: Mentor

Maybe in some cases, but this isn't one of them. You can always get documentation about any of the built-in functions of Excel by clicking the ? icon. Every spreadsheet function is documented, and shows an example of how to use it.

10. Jun 13, 2016

### Square1

I don't have trouble with using it when my data is in vertical form like in the example you gave A1:A10, multiplied by B1:B10.

My data is horizontal. I need to multiply A1*B1 add it to C1*D1 add it to E1*F1...etc.

11. Jun 13, 2016

### Square1

It works if you only use A1 as array 1, and B1 as array 2 (=sumproduct(A1,B1)).

But if I try to include the cells to the right of those two, it doesn't catch the pattern and start to multiply the two, and add the product to a1*b1 (answer is way off) ex: =sumproduct(a1,b1,c1,d1).

I keep trying to say this, but I don't have any cells below a1 or b1 that I need in this formula. Everything is going to the right, not down.

12. Jun 14, 2016

### Staff: Mentor

I don't believe it's possible to do this using sumproduct(). It expects the data for one matrix to not be interwoven with the data from another matrix. In other words, the data for each matrix has to be in a rectangular form, and separate from the data in the other matrix. From the documentation for sumproduct(): "The array arguments must have the same dimensions."
I interpret this to mean that if one array is 1 X 4 (one row and four columns), the second array also has to be 1 X 4. A second array that is 4 X 1 (four rows and one column) doesn't work, and produces a value of #VALUE!.

The best you can hope for is to write a macro in VBA (Visual Basic for Applications). Either that, or find some way to get the data arranged in some way that a built-in function such as sumproduct() can work with it.

13. Jun 14, 2016

### pasmith

An array formula which might work is:

=SUM(IF(MOD(COLUMN(A1:D1),2)=1,A1:D1*B1:E1,0))

The IF function should return an array {A1*B1,0,C1*D1,0}, which when summed yields A1*B1 + C1*D1 as required. Perhaps you could even

=SUM(MOD(COLUMN(A1:D1),2)*A1:D1*B1:E1)

However reorganising the layout of your spreadsheet so that you can do the SUMPRODUCT of column A and column B is preferable if you can conveniently do it.

14. Jun 14, 2016

### Staff: Mentor

This...

15. Jun 15, 2016

### Square1

Yes mark and pasmith. I mean the best solution is just to rearrange the data. I appreciate the function that have been suggested to me! But I will want to keep it a bit simpler than that. Its weird though because when I first started making the sheet, I was copying out data from another sheet, and I was getting error messages saying something about the new cells being improperly sized for the incoming cells...so I just went with a quick go around and started transposing all the pasted data and excel left me alone after...

I'm most likely gonna start exploring this in the future (both for improving my abilities and just out of interest).