CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
user2545345
user2545345

Reputation: 61

Excel: Multiply each cell in row X by corresponding cell in row 2, and get sum

I have a table set up as follows:

Column 1 - Customer Name
Row 1 - Item Name
Row 2 - Item Cost
Row 3+- Item Quantity

How do I set up the last column to calculate the total cost for each customer? I.e, For each customer row, I want to multiply the number in each cell (= quantity) by the corresponding cell in Row 2 (= cost), and add them all up for the final bill.

To clarify what I'm saying I'm attaching the following picture so that we can discuss specifics. enter image description here

Upvotes: 4

Views: 51785

Answers (4)

ian0411
ian0411

Reputation: 4265

One way is to use this formula:

=SUM(B4:B5)*B2+SUM(C4:C5)*C2

It is not so cool but you still need to expand the formula even with SUMPRODUCT because the range has to be the same as far as I know.

The other way I came up will use a matrix function called MMULT and here is the example:

With this array (means you have to click Ctrl + Shift + Enter altogether) formula entered into cell D6: =SUM(MMULT(B2:C2,TRANSPOSE(B3:C5))), you will get your expected result without needing all the subtotals. Please note this is a 2 x 1 By 2 x 3 Matrixformula.

Upvotes: 0

Kai Vincent
Kai Vincent

Reputation: 1

If, for example, your first data set is in column A (i.e. per unit cost) and the second data set is in column B (i.e. quantity), and you want the total cost for each item for the specified quantity, place the following formula in C1

=A1*B1

Select C1 and drag the fill handle - this is the small black square at the bottom right corner of the cursor as far down the column as you need. The program will automatically replicate the formula with the correct cell numbers for each row.

Upvotes: 0

user3636311
user3636311

Reputation: 1

You can use sumproduct but specify the ranges, e.g. =sumproduct(B2:B6,C2:C6), the next row would then be =sumproduct(B2:B6,D2:D6) etc. I'm sure there's a way to "fix" your cost row but it's quite quick doing it this way

Upvotes: 0

barry houdini
barry houdini

Reputation: 46361

Have you tried SUMPRODUCT - it does exactly what you need, gives the sum of 2 or more multiplied ranges?

=SUMPRODUCT(A71:C71,$A$2:$C$2)

You can extend the ranges as far as you need. If you want to add columns make sure you don't add at the end, e.g. if you retain one blank column (D currently) and include that in the formula, then if you add a column at D the formula will automatically extend to E

Upvotes: 11

Related Questions