Sumproduct problem

[paraxis]

Hi all,
i am currently working on cost analysis of large projects.
I have a worksheet with about 100 headings and i would like to sum up the totals of 'quantity x unit price' for each product.
I tried the sumproduct function but it did not work and i had to do the '=sum(b4*c4,d4*e4,f4*g4,..........) to get the result in the total column. I attach a small sample of the file. Is there a way that i can use some function to get my total instead of the sum function i am using?
Thanks to all
paraxis

[teylyn]

paraxis,

because your ranges are not contiguous, i.e. you want to multply every other column with the column next to it, it's kinda hard to figure out a better formula than the one you already have.

Is the data manually entered in the table you are presenting? Or is this table a summary of data entered elsewhere? If the latter, there may be an easier way of calculating the line totals based on the original data input.

cheers

[zbor]

Here, try this:

=SUMPRODUCT(B7:K7;C7:L7;MOD(COLUMN($A$1:$J$1);2))

Pull down and format in €

[teylyn]

Nice one, zboric!!

paraxis, if zbor's formula throws an error, try replacing the semicolons with commas to suit your Excel settings, like this

=SUMPRODUCT(B7:K7,C7:L7,MOD(COLUMN($A$1:$J$1),2))

[paraxis]

Thanks, tried your formula but is not working
10x Paraxis

[zbor]

How do you mean not working... check my signature

PROJECT%20COST(1).xls

[paraxis]

Hi Teylyn,
thanks for your formula it works but if i put it column L it gives me a circular reference obviously. Do you know what might be wrong in the formula?
Paraxis

[teylyn]

circular reference is because both me and zbor put the formula in a cell other than column K. To use in column L try


=SUMPRODUCT(B7:J7,C7:K7,MOD(COLUMN($A$1:$I$1),2))

when you apply it to your original sheet, adjust the ranges to be the same number of columns, stopping before the totals column.

[zbor]

Ahh, yes add one more column And put formula in M ...

Or change formula into: =SUMPRODUCT(A7:J7,B7:K7,MOD(COLUMN($B$1:$K$1),2))

[paraxis]

Teylyn
Thanks tried & worked fantastic. Thanks to all and have a nice week.
Paraxis