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.

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.

On Jun 17, 5:04*pm, Joel wrote:
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.- Hide quoted text -

- Show quoted text -

Joel with little modification to the ranges, I have the grid.
Thanks : )

Joel,

The OP is happy with your solution so this is not meant as a critique.

I had to make your ranges Absolute in Rows as well as Columns to stop them
indexing when I dragged the formula on the fill handle, (ie $A$1:$A10000),
otherwise it missed some data.

Even with only the years 1980 - 1983, with Jan - Dec it took about 5 seconds
to update on my system at every data entry because it was calculating
480,000 times.

It would seem that this is a case where you were right in the ~misc group
(http://tinyurl.com/5lamuk) whereby, as Bob Philips said, VBA *can* be
faster in some instances, although you seemed be making your statement in
~misc general as if VBA was *always* faster.

--
Regards,

Sandy

In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Joel" wrote in message
...
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.