Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Sumproduct help in Excel

  1. Jun 12, 2016 #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...
     
  2. jcsd
  3. Jun 12, 2016 #2

    fresh_42

    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
  4. Jun 12, 2016 #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?
     
  5. Jun 12, 2016 #4

    fresh_42

    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.
     
  6. Jun 12, 2016 #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!
     
  7. Jun 12, 2016 #6

    fresh_42

    Staff: Mentor

    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.
     
  8. Jun 12, 2016 #7

    fresh_42

    Staff: Mentor

    Eureka! Got it! SUMPRODUCT(A1:A10;B1:B10)
     
  9. Jun 13, 2016 #8

    Mark44

    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>)"
     
  10. Jun 13, 2016 #9

    Mark44

    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.
     
  11. Jun 13, 2016 #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.
     
  12. Jun 13, 2016 #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).

    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.
     
  13. Jun 14, 2016 #12

    Mark44

    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.
     
  14. Jun 14, 2016 #13

    pasmith

    User Avatar
    Homework Helper

    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.
     
  15. Jun 14, 2016 #14

    Mark44

    Staff: Mentor

    This...
     
  16. Jun 15, 2016 #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...

    I'm most likely gonna start exploring this in the future (both for improving my abilities and just out of interest).
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: Sumproduct help in Excel
  1. I need help with Excel (Replies: 2)

Loading...