Try =SUMPRODUCT(A2:A9,B2:B9)
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
Cheers
Try =SUMPRODUCT(A2:A9,B2:B9)
Last edited by mikerickson; Sep 8th, 2019 at 08:34 PM.
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
Like this thread? Share it with others