Summing up a group of numbers

[ramanan256]

I have a sheet containing columns of numbers as follows:
r p q w q*w total
1 289 20 0.210 4.200 (4.2+4.53+0.81) this is for 1
1 183 18 0.252 4.536 (3.37+1.69+0.82+2.43) this is for 2
1 082 02 0.405 0.810 (0.932+6.804+14.028) this is for 3
2 050 08 0.484 3.872 : for r= 4
2 301 14 0.121 1.694 : for r = 5
2 067 05 0.164 0.820
2 149 16 0.152 2.432
3 031 02 0.466 0.932
3 399 36 0.189 6.804
3 403 42 0.334 14.02


I have to add up all cells of column q*w containing r value as 1 and then group containing r value as 2 and then 3 and so on...

I have attached a sample sheet...I would grateful for somebody who helps me out with this....

Thanking you,
Ramanan

[Paul]

Ramanan,

In your sample worksheet the numbers in column B repeat themselves. There are groups of cells starting with 1, 2, 3.. up to 9, and then it goes back to 1. It does this multiple times.

Is this how your real data is laid out, and do you only want the sum for each specific grouping of 1's, 2's, etc.? Or do you want the sum for ALL 1's in column B, then ALL 2's in column B, etc.?

I un-hid column A and that may help with the SUM groups.. it appears that the 1-9 groups in column B share the same value in column A, and once the value in column A changes the numbers in column B reset to 1.

In such a case, you could probably use this to sum the first grouping of 1's

=SUMIFS(H:H,A:A,1,B:B,1)

The first grouping of 2's would be:

=SUMIFS(H:H,A:A,1,B:B,2)

Etc. on down to 9:

=SUMIFS(H:H,A:A,1,B:B,9)

The second set of 1's (where column A is 2) would be:

=SUMIFS(H:H,A:A,2,B:B,1)

[Charlie_Howell]

try this version

[ramanan256]

Dear Paul,
Acc to your reply, I am looking into the sets in column B alone i.e. ....."The second set of 1's (where column A is 2) would be:".
I know that they do go back to 1...I just hide column A to avoid confusion...

Yes, my real data that I have got looks exactly like this.....and I only want the sum for "each specific" grouping of 1's, 2's

If you look at my attachement, you could see that the first three cells under column "total" where I would have showed a sample summing....

Cant there be a formula where I can just drag down the cells and get the values for each set.???

Thanking you,
Ramanan

[ramanan256]

Dear Charlie,

Thanks for your efforts but this is not what I am looking for. I just need to sum up the values of q*w when r=1 then when r=2 in the next cell, followed by r=3 and so on....But I would like to mention that the value of r would revert back to 1 again ...its like a cycle....
I need sum for each individual r=1, 2, 3...if you look at the sample atrtachment that I have made...I would have done it for r=1, then r=2 and r=3...

P.S.: I do not need to sum up whole of r=1 wherever they appear in column B.. its the specific group that you see there that I want to sum up...

[ramanan256]

Dear Paul,
Would column A be of any help? Where you can say that when columA=1, ColumB=1 sum up ..so on....

Thanks,
Ramanan

[DonkeyOte]

Why not just use a Pivot Table ? Example attached - you can obviously modify layout to suit.