Summing of multiple columns

[SloopJohnB]

In the attached spreadsheet I would like to calculate the totals for pipe and valves by service & size (Columns A,B D & D) from the groups of paired columns Pipes & Valves under the headings of SD0-1, SD0-2, SD0-3 etc etc to LP-SD8 multiplied by the number of Service droppers (Highlighted in red) with the totals in columns G & H.

I can individually write a formula such as G7 = (I7*I4)+( K7*K4) etc etc, but is there a way to lookup up the data cells?

Thanks John

[alvin-chung]

Hi SloopJohnB, G7 returned #VALUE!
Could you upload again sample spreadsheet with some expected result?

[benishiryo]

hi John. maybe you could manually key in some answers you are hoping to see so that we know we're on the right track. you mentioned:

(I7*I4)+( K7*K4)

but row 4 are texts. i'm guessing it's row 3. so in G5:
=SUMPRODUCT((I$4:AJ$4=G$4)*$I$3:$AJ$3*$I5:$AJ5)
copy down & across

also, could you update your profile for the MS Off Ver? helps us give you newer & more efficient formulas

[SloopJohnB]

Hi SloopJohnB, G7 returned #VALUE!
Could you upload again sample spreadsheet with some expected result?

By long hand I get the following values
G7 = 110
H7 = 5
G9 = 1386
H9 = 63

[benishiryo]

correction, my formula should be:
=SUMPRODUCT((I$4:AI$4=G$4)*$I$3:$AI$3*I5:AI5)

how do you get the G9 results though? manually, isn't it
=I9*5+K9*7+M9*2+W9*12+Y9*15+AA9*4+AG9*9+AI9*4

that's 968

[SloopJohnB]

Thanks

I tried your formula, but the result came up with #VALUE#

I stand corrected on my manually calculation

G9 = 1078

But you missed a couple of cells

=I9*5+K9*7+M9*2+o9*10+W9*12+Y9*15+AA9*4+AG9*9+AI9*4

[SloopJohnB]

Thanks benishiryo

All is working.

[alvin-chung]

By long hand I get the following values
G7 = 110
H7 = 5
G9 = 1386
H9 = 63

No wonder I'm confused, your G7 mentioned above is actually G6 in the spreadsheet that you've uploaded...
In case you're still interested on variations of formula, try these and copy down to rest of the rows

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin