Complex sum

G

Greshter

Hi All,
Sorry I accidentally posted that last message before I finished. I am trying to complete a complex sum and need some assistance. I need to sum a series of areas based on an id field. That may sound easy enough using either subtotals or a pivot table however I need to make cumulative sums as well. To give you an idea of what I am trying to here is a sample of the data I am using:

ID Sub_ID Area 1 Area 2
S1 S1_1 25,885.26 7,765.6
S1 S1_1 120,209.57 36,062.9
S1 S1_1 4,620,365.19 1,386,109.6
S1 S1_1 3,580,189.81 358,019.0
S1 S1_2 4,104,332.39 1,231,299.7
S1 S1_3 14,428.65 1,442.9
S1 S1_3 1,023,600.61 307,080.2
S1 S1_3 7,561,036.12 2,268,310.8
S10 S10_1 1,958,449.03 195,844.9
S10 S10_2 194,607.66 58,382.3
S10 S10_2 4,724,115.45 472,411.5
S10 S10_3 5,728,707.20 572,870.7
S11 S11_1 1,554,378.27 155,437.8
S11 S11_2 1,640,711.70 164,071.2
S11 S11_3 1,774,008.61 177,400.9
Click to expand...

I need to sum Area 2 at each interval of Sub_ID. I also need to sum
Area 1 in the pattern of

Sum S1_1 Then
Sum S1_1 + Sum S1_2 Then
Sum S1_1 + Sum S1_2 + Sum S1_3

Then start over for the next division of values so:

Sum S10_1 Then
Sum S10_1 + Sum S10_2 Then
Sum S10_1 + Sum S10_2 + Sum S10_3

And so on ...

I should make you aware there can be up to 5 values that need to be
summed together but I don't think there are anymore than that.

I hope I've explained it well enough.

Any potential solutions would be greatly appreciated.

Cheers,
Mike
 
J

Joel

I put your table at cell1 A1:C16 including the header row. I put the formula
below at cell D2. then I copies D2 to the range D2:F16. It turns out the
same formula can be used to sum both Area 1 and Area 2.


=IF(EXACT(A1,A2),E1+C2,C2)
 
Top