Drag the sum value question??

[mikaer0722]

Hello Guys,

I have a question to drag sum value.

P1 P1 P1 P1 P2 P2 P2 P2 P3 P3 P3 P3............Pn
2 2 2 2 3 3 3 3 4 4 4 4.................

From the above table, I want to get the sum value into a small table (below one)

P1 Total P2 Total P3 Total ....................Pn Total
=sum(A2:D2) =sum (E2:H2) =sum(I2:L2)

Is there any way I can drag the sum formula all the way to the end, but start column of each sum total will be the column next to end column of previous sum total (see the above formula).

I tried to drag them directly, but it didn't work. The P2 Total=sum(B2:E2), which is not what I want.

Thanks!

[mikaer0722]

Sorry about the messy table format,

For the small table, the first row is PQ1 total, PQ2 total, PQ3 total..........., and second row is the sum formula : PQ1 total=sum(A2:D2), PQ2 total=sum (E2:H2) ......................

[JeteMc]

Try this formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

[mikaer0722]

Hello,

Sorry, It doesn't work well. I attached the working file. If you can have a look, maybe it would be clear for my questions.

Thanks

[hemesh]

try below

Please Login or Register  to view this content.

drag to right

Left(A5,2)= will give "P1" from P1 Total

remove "....." from M1 and M2

[newdoverman]

There are a couple of ways (or more) of interpreting what you want.
This workbook has 3 different variations on what I think that you want.

First table A5:B8
Enter in B6 and fill down

Please Login or Register  to view this content.

Second table D5:E8
Enter in E6 and fill down

Please Login or Register  to view this content.

Both of the above tables are data tables and have a totals row to give the total of all the values.

Third table G5:J6
Enter in G6 and fill across

Please Login or Register  to view this content.

At the end of the table in J2 enter this formula

Please Login or Register  to view this content.

The reference $1:$1 refers to the entire row 1 of the worksheet.
The reference $2:$2 refers to the entire row 2 of the worksheet.

[JeteMc]

It is good to see that you have gotten several ideas about how to do this from others, however I don't see why you say that mine doesn't work. When I paste the formula I gave you in A6 and drag it to C6 I get values of 8, 12 and 16 respectively, so it would appear to work exactly as requested in post #1.

[newdoverman]

JeteMc's formula works just fine. There isn't a grand total but as for totalling P1, P2 etc., it works perfectly.

Why do you want to have specific references to ranges like you state in the problem? It is able to be accomplished (JeteMc) but it "hand-cuffs" you if you have to change and have more columns of some values and less of others.

Another question is; how do you expect to use the results of the calculations?