Sum row on change of account (Excel 365)

[henryg]

I want to get an accumulating sum of rows in the balance column where, in this case, the account number is the same. For example,

Day	Account	Dr	Cr	Balance
1	1000	100	75	? (25)
1	1000	500	260	? (265)
4	1150	400	100	? (300) <tbody> </tbody>
5	1150	800	600	? (500) <tbody> </tbody>
5	2000	1000	25	? (975) <tbody> </tbody>

<tbody>
</tbody>

This must be an old chesnut, but I can't work it out.

 

[RoryA]

Assuming your data is sorted by account, you can simply test that:

=IF(B2=B1,E1+D2-C2,D2-C2)

assuming your posted data is in columns A:E with E being the formula column.

 

[tyija1995]

Hey,

Try this formula in cell E2 (Balance column):
=SUMPRODUCT(($B$2:B2=B2)*($C$2:C2-$D$2:D2))
And drag that down the E column (Assuming that B is your Account number and C & D are Dr and Cr respectively.

 

[Sanjeev1976]

You can also try the below formula in E2 and drag down :

=SUMIF($B$2:B2,B2,$C$2:C2)-SUMIF($B$2:B2,B2,$D$2:D2)

 

[henryg]

Many thanks, I'll give them a go.

The sumif looks similar to what I tried inc locked start ranges, although I always use sumifs these days even if only one crireria, but I always ended up with a nil value. I wonder if there was some corruption or I just messed up (latter probable ). I also played with sumproduct, but not above way.

Thanks again.

 

[henryg]

Later...sumif/s works and as expected. I am sure sumproduct will work too but I find I understand sumif/s formula quicker when I look back later.

I am bemused why I failed yesterday.