SUMPRODUCT in Excel: Multiply & Add Values Easily

  • Thread starter Square1
  • Start date
  • Tags
    Excel
In summary, you are having difficulty using the sumproduct function in excel, because the syntax requires that the arrays be of the same size and shape.
  • #1
143
1
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...
 
Physics news on Phys.org
  • #2
Square1 said:
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)
 
Last edited:
  • #3
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
Square1 said:
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.
 
  • #5
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
Square1 said:
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 :nb) 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.
 
  • Like
Likes Square1
  • #7
Square1 said:
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)
 
  • #8
fresh_42 said:
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>)"
 
  • #9
Square1 said:
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.
Square1 said:
No it's not a big deal but come on Microsoft...
 
  • #10
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
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).

fresh_42 said:
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.
 
  • #12
Square1 said:
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.
 
  • Like
Likes Square1
  • #13
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.
 
  • Like
Likes Square1
  • #14
pasmith said:
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...
 
  • #15
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 :oldlaugh: and excel left me alone after...

Mark44 said:
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).
 

1. What is the purpose of SUMPRODUCT in Excel?

SUMPRODUCT is an Excel function that allows you to multiply corresponding values from multiple arrays and then sum the products. This is useful when you want to calculate the total value of a set of data that is dependent on multiple factors.

2. How do you use SUMPRODUCT in Excel?

To use SUMPRODUCT in Excel, you need to specify the arrays or ranges of cells that you want to multiply and then sum. For example, the formula =SUMPRODUCT(A1:A5, B1:B5) will multiply the values in cells A1 to A5 with the values in cells B1 to B5 and then sum the products.

3. Can you use SUMPRODUCT to multiply and add values from different worksheets?

Yes, you can use SUMPRODUCT to multiply and add values from different worksheets in Excel. You just need to reference the cells in each worksheet in the formula. For example, the formula =SUMPRODUCT(Sheet1!A1:A5, Sheet2!B1:B5) will multiply the values in cells A1 to A5 in Sheet1 with the values in cells B1 to B5 in Sheet2 and then sum the products.

4. Are there any limitations to using SUMPRODUCT in Excel?

There are a few limitations to using SUMPRODUCT in Excel. First, it only works with numerical data, so it cannot be used with text or other non-numerical values. Second, it can only handle up to 30 arrays in a single formula. Lastly, it may slow down the performance of your Excel workbook if used with a large dataset.

5. Can SUMPRODUCT be used for conditional multiplication and addition in Excel?

Yes, you can use SUMPRODUCT for conditional multiplication and addition in Excel by using logical operators, such as equals (=), greater than (>), less than (<), etc. For example, the formula =SUMPRODUCT((A1:A5>50), B1:B5) will only multiply and sum the values in cells B1 to B5 if the corresponding value in cells A1 to A5 is greater than 50.

Suggested for: SUMPRODUCT in Excel: Multiply & Add Values Easily

Replies
2
Views
2K
Replies
7
Views
1K
Replies
17
Views
3K
Replies
7
Views
2K
Replies
18
Views
5K
Replies
3
Views
2K
Replies
1
Views
1K
Replies
3
Views
1K
Replies
11
Views
5K
Back
Top