Running balance table, monthly/year grid
You can do this with a sumproduct formula
Put in row 1 starting in column F the years 1980. Put in Column E the names
of the months starting in Row 2. Make the months names either all 3
characters (Jan, Feb,MAR) or spelled out completely (change MMM below to
MMMM). You have a mixture off both type month names in your data.
Put the following formula in cell F2
=SumProduct(--(year($A1:$A10000)=F$1),--($E2 =
text($A1:$A10000,"MMM")),$B1:$B10000)
"Sinner" wrote:
Hi
Data
----------------------------------------------------
column1 column2 column3
1/1/1980 1000 collected
4/1/1981 -500 entered
6/2/1980 200 collected
4/2/1983 -1500 entered
7/2/1982 2100 collected
9/5/1980 -5000 entered
6/3/1980 2000 collected
-----------------------------------------------------
I want to list running balances like
1980 1981 1982
Jan (amount) (amount) (amount)
Feb (amount) (amount) (amount)
March (amount) (amount) (amount)
April (amount) (amount) (amount)
Thx.
|