Multiplying two cells at a time and adding each one.

dmb41

Board Regular
Joined
Nov 10, 2010
Messages
75
Is there a short cut to doing the following?

e.g.
B C
4 8 1
5 4 1
6 2 0
7 1 0


The actual problem is much larger ... but what I am trying to do is multiply
(8 *1) + (4*1) + (2*0) + (1*0) to Get a total for B,C, etc...

Is there a shortcut instead of doing: =$B$4*C4+$B$5*C5+$B$6*C6+$B$7*C7

and

=$B$4*D4+$B$5*D5+$B$6*D6+$B$7*D7

etc for each. As I have over 400 rows.
<table style="width: 64px; height: 20px;" border="0" cellpadding="0" cellspacing="0"><col width="64"><tr height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" align="right" height="20">
</td> </tr></table>
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
Excel Workbook
ABCDEFG
148112<< Do you mean Something like this?
2541
3620
4710
Sheet1
Excel 2007
Cell Formulas
RangeFormula
F1=SUMPRODUCT(B1:B4,C1:C4)
 
Upvote 0

UniMord

Active Member
Joined
May 6, 2002
Messages
311
If you're looking to do enter one formula for the entire table, enter:

=SUM(B4:B404 * C4:C404, B4:B404 * D4:D404)

and hit Control+Shift+Enter to make it an array formula.
 
Upvote 0

dmb41

Board Regular
Joined
Nov 10, 2010
Messages
75
Thanks that's the function I was trying to remember. Exactly what I was looking for, for the rows.

Also used this: =SUM($A$3:$A$473*C3:C473)

May be the same?

Thanks again!
 
Upvote 0

Forum statistics

Threads
1,191,388
Messages
5,986,321
Members
440,017
Latest member
vasanrajeswaran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top