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,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Account[/TD]
[TD]Dr[/TD]
[TD]Cr[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1000[/TD]
[TD]100[/TD]
[TD]75[/TD]
[TD]? (25)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1000[/TD]
[TD]500[/TD]
[TD]260[/TD]
[TD]? (265)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1150[/TD]
[TD]400[/TD]
[TD]100[/TD]
[TD]

? (300)

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1150[/TD]
[TD]800[/TD]
[TD]600[/TD]
[TD]

? (500)

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2000[/TD]
[TD]1000[/TD]
[TD]25[/TD]
[TD]

? (975)

<tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

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.