# Sum of Multiply Cells

#### NZAS

##### Board Regular
Hi

I have a data set with several columns of data that I am looking for formula for that will give sum of all cell after cells in column A and B have been multiplied (i.e. a3*b3 etc to get the sum of all) across all sets of columns, to shorten the formula from what is currently being used which is (A1*B1)+(A2*B2) etc. etc. as it takes a lot when there are 50 + rows to each set of 2 columns and there are several sets of these that need the sum for each 2 columns in the worksheet.
I could create anew column to do the multiplying then sum that column, But then I need to hide this.
Hope I can be helped with this

The required answer of this data set would be 7400

A B SUM
 3 370 1100 3 370 1100 3 370 1100 3 370 1100 3 370 1100 3 370 1100 1 370 370 1 370 370

<tbody>
</tbody>

Cheers

#### mikerickson

##### MrExcel MVP
Try =SUMPRODUCT(A2:A9,B2:B9)

Last edited:
• NZAS

#### NZAS

##### Board Regular
Thanks mikerickson
That has done the job for me
Could I exchange the data ranges in the formula with names ranges and would it still work
or would this formula work if i was to replace the ranges with named ranges or i am thinking of the data being down loaded from a database and this would then end up as a table of data

Cheers
NZAS

#### mikerickson

##### MrExcel MVP
...Could I exchange the data ranges in the formula with names ranges and would it still work
or would this formula work if i was to replace the ranges with named ranges...
What happened when you tried it?