Sum by month running balance

[Sinner]

Hi,

I have a worksheet in which I have a date column, amount column &
amount as entered/collected column.

column1 column2 column3
1/1/1980 1000 collected
4/1/1980 -500 entered
6/2/1980 200 collected
4/2/1980 -1500 entered
7/2/1980 2100 collected
9/5/1980 -5000 entered
6/3/1980 2000 collected



and so on.

Result:
Something like running balance. at the end for fifth month it should
show -1700

Column5 column6
January 500
February 1300
March 3300
April 3300
May -1700

No pivot table pls : )

 

[Don Guillett]

One way
in d2=b2
in d3=d2+b3 copy down

 

[Mike H]

hi,

Try this wth jan etc in E1 down
in F1

=SUMPRODUCT((MONTH($A$1:$A$7)=ROW(A1))*($B$1:$B$7))

and then this in F2 and drag down
=E1+SUMPRODUCT((MONTH($A$1:$A$7)=ROW(A2))*($B$1:$B$7))

Mike

 

[Sinner]

hi,

Try this wth jan etc in E1 down
in F1

=SUMPRODUCT((MONTH($A$1:$A$7)=ROW(A1))*($B$1:$B$7))

and then this in F2 and drag down
=E1+SUMPRODUCT((MONTH($A$1:$A$7)=ROW(A2))*($B$1:$B$7))

Mike











- Show quoted text -

Hi,

A little modification for yearly match.

I want to list same balances monthly/year like

1980
Jan (amount)
Feb (amount)
March (amount)
April (amount)

1981
Jan (amount)
Feb (amount)
March (amount)
April (amount)

1982
Jan (amount)
Feb (amount)
March (amount)
April (amount)