SUMPRODUCT in Excel: Multiply & Add Values Easily

  • Thread starter Thread starter Square1
  • Start date Start date
  • Tags Tags
    Excel
Click For Summary

Discussion Overview

The discussion revolves around the use of the SUMPRODUCT function in Excel, particularly focusing on challenges faced when attempting to multiply and add values arranged horizontally. Participants explore various methods and potential limitations of the function, as well as alternative approaches to achieve the desired calculations.

Discussion Character

  • Technical explanation
  • Debate/contested
  • Mathematical reasoning

Main Points Raised

  • Some participants express difficulties with the syntax of the SUMPRODUCT function when using horizontal data arrangements.
  • Others suggest using additional columns to calculate products separately before summing them, although this may not be ideal for all users.
  • A few participants question whether the orientation of data (horizontal vs. vertical) affects the functionality of SUMPRODUCT.
  • One participant proposes an array formula as a potential solution, although they note they cannot test it without access to Excel.
  • Some participants mention the possibility of using VBA macros as an alternative method for handling the calculations.
  • There is discussion about the limitations of Excel and the need for proper data arrangement to avoid errors.

Areas of Agreement / Disagreement

Participants generally do not reach a consensus on the best approach to use the SUMPRODUCT function with horizontal data. Multiple competing views and suggestions remain, with some advocating for rearranging data while others explore alternative formulas.

Contextual Notes

Participants note that the SUMPRODUCT function requires arrays to have the same dimensions, which complicates its use with interwoven data. There are also references to error messages encountered when data is improperly sized.

Who May Find This Useful

This discussion may be useful for Excel users facing challenges with the SUMPRODUCT function, particularly those working with horizontally arranged data and looking for alternative methods or solutions.

Square1
Messages
143
Reaction score
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
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:
Is there really no other way to use the sumproduct function? Does having my data in horizontal form have something to do with it?
 
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.
 
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!
 
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   Reactions: Square1
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)
 
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>)"
 
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   Reactions: 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   Reactions: 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).
 

Similar threads

Superscript Formula in Excel Function CONCATENATE
  • · Replies 5 ·
Replies
5
Views
14K
What engineering and math features is Excel missing?
  • · Replies 17 ·
Replies
17
Views
4K
Undergrad How Do You Calculate Day-Specific Phase Shifts in Excel for Sine Waves?
  • · Replies 7 ·
Replies
7
Views
3K
MATLAB Solving MATLAB to Excel Copy Issues
  • · Replies 18 ·
Replies
18
Views
6K
MHB Excel Question (asking for a friend)
  • · Replies 2 ·
Replies
2
Views
3K
I want to interpolate two values in Excel
  • · Replies 4 ·
Replies
4
Views
1K
Should Excel macros self-adjust when you delete lines above the affected cells?
  • · Replies 27 ·
Replies
27
Views
4K
Microsoft Excel background grid color default reverts
  • · Replies 10 ·
Replies
10
Views
3K
MATLAB Converting Excel formulae into Matlab code
  • · Replies 2 ·
Replies
2
Views
4K
Undergrad Simulation of CHSH inequality experiment in Excel
  • · Replies 61 ·
3
Replies
61
Views
5K