Summary in formula bringing sum of duplicates

[inspi]

Hi,
Table structure is like this:

Col1 | 1_Cnt | Col2 | 2_Cnt | Col3 | 3_Cnt
---- ----- ---- ----- ---- -----
x | 65 | a | 21 | m | 18
x | 65 | a | 21 | n | 3
x | 65 | b | 29 | o | 21
x | 65 | b | 29 | p | 8
x | 65 | c | 5 | q | 5
x | 65 | d | 4 | r | 4
x | 65 | e | 4 | s | 4

In the report I am grouping by Col1 and Col2 then in the report at GH1 level I am displaying group 1 name x and 1_cnt which is 65. In GH2 level I am displaying group 2 name and 2_cnt which is coming like this:
a | 21
b | 29
c | 5
d | 4
e | 4

Now, my question is I would like to get the sum(2_Cnt) which should be 63 (ie., 21+29+5+4+4 because for a and b we have 2 records but while doing sum of I have to consider it only once)but I am getting 113(ie., 21+21+29+29+5+4+4). I am writing this formula:

sum({2_Cnt},{Col1})

this is to get sum(2_Cnt) at each group level of Col1.

Is there any way to get the sum as 63 instead of 113? Can somebody please help me to solve this issue?
This is urgent.Appreciate your immediate response!
Thanks!

 

[IanWaterman]

use a Running total and in the evaluate section select on Change of Col2.

Ian

 

[inspi]

I tried this and it is working fine for one of my test scenario. Thanks Ian!
But could somebody give me an idea about the behavior of running total. How does it works when I place this in GH2 and GH1. In this scenario I want to get the sum(2_Cnt) for distinct Col2 values and I would like to display this sum in GF1.Is there going be any difference in sum between displaying in GH2 and GF1.

 

[lbass]

Set up the rt using sum of 2_cnt, evaluate on change of group: col2, reset on change of group #1 and place this in the Group FOOTER #1. You only need to place this in GF#1 to get the correct result.

-LB

 

[inspi]

Thanks LB!