- #1

- 144

- 1

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 are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- Thread starter Square1
- Start date

- #1

- 144

- 1

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

- 16,436

- 15,483

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

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

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

- 144

- 1

- #4

- 16,436

- 15,483

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

- 144

- 1

- #6

- 16,436

- 15,483

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

- 16,436

- 15,483

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

- #8

Mark44

Mentor

- 36,019

- 7,946

Which you can find by getting help for "sumproduct" right in Excel. Both arrays have to be the same size and shape.Eureka! Got it! SUMPRODUCT(A1:A10;B1:B10)

The syntax is "=sumproduct(<upper left cell A>: <lower right cell A>, <upper left cell B>: <lower right cell B>)"

- #9

Mark44

Mentor

- 36,019

- 7,946

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.Ughhh...sometimes I am amazed how limited excel is some of its utility.

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

- #10

- 144

- 1

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

- #11

- 144

- 1

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.SUMPRODUCT(A1:A10;B1:B10)

- #12

Mark44

Mentor

- 36,019

- 7,946

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

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

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

pasmith

Homework Helper

- 2,205

- 809

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

- #14

Mark44

Mentor

- 36,019

- 7,946

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

- 144

- 1

I'm most likely gonna start exploring this in the future (both for improving my abilities and just out of interest).The best you can hope for is to write a macro in VBA (Visual Basic for Applications).

Share: