SUMPRODUCT in Excel: Multiply & Add Values Easily

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

 

[fresh_42]

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

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)

 

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

 

[fresh_42]

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

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.

 

[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!

 

[fresh_42]

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!

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.

 

[fresh_42]

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!

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

 

[Mark44]

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

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>)"

 

[Mark44]

Ughhh...sometimes I am amazed how limited excel is some of its utility.

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.

No it's not a big deal but come on Microsoft...

 

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

 

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

SUMPRODUCT(A1:A10;B1:B10)

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.

 

[Mark44]

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.

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.

 

[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)

I don't have access to Excel at home to test these.

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.

 

[Mark44]

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.

This...

 

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

The best you can hope for is to write a macro in VBA (Visual Basic for Applications).

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