I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A B
01/01/09 3
02/01/09 2
19/01/09 7
09/02/09 3
18/02/09 1

TOTAL = 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

So when we get past March 9th next month, the total will be 1.

You don't have any entries in your sample data for the month of March so how
do you arrive at that result?

So when we get past March 9th

Why March 9th?

If you want to sum based on the *current* month:

=SUMPRODUCT(--(MONTH(A1:A100)=MONTH(NOW())),B1:B100)

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A B
01/01/09 3
02/01/09 2
19/01/09 7
09/02/09 3
18/02/09 1

TOTAL = 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

The result is from today. March 9th is an example, so when you get
past that date the data changes. The formula you have created works
fine, thanks for your help and looking into my problem.

On Feb 26, 7:35*pm, "T. Valko" wrote:
So when we get past March 9th next month, the total will be 1.

You don't have any entries in your sample data for the month of March so how
do you arrive at that result?

So when we get past March 9th

Why March 9th?

If you want to sum based on the *current* month:

=SUMPRODUCT(--(MONTH(A1:A100)=MONTH(NOW())),B1:B100)

--
Biff
Microsoft Excel MVP

"Steve" wrote in message

...

I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A * * * * * * * * * * * * * * * * * B
01/01/09 * * * * * * * * * * * * 3
02/01/09 * * * * * * * * * * * * 2
19/01/09 * * * * * * * * * * * * 7
09/02/09 * * * * * * * * * * * * 3
18/02/09 * * * * * * * * * * * * 1

TOTAL = * * * * * * * * * * * * 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

Steve,

As Biff has pointed out your question lacks clarity. In your sample data
apart from Biff's point why isn't the answer 11? The last 3 dates are all
within 1 rolling month.

I also have some difficulty with the concept of rolling month, what is it?
30 days, 31 days, 29 days or even 28 days

Anyway, I'll propose a solution you can ponder on

=SUM(IF(A1:A100=LOOKUP(6.022*10^23,A1:A100)-30,B1:B100))

This array enterd formula takes the last date in column A and sums column B
for all dates within the 30 days previous of that.

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Steve" wrote:

I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A B
01/01/09 3
02/01/09 2
19/01/09 7
09/02/09 3
18/02/09 1

TOTAL = 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
The result is from today. March 9th is an example, so when you get
past that date the data changes. The formula you have created works
fine, thanks for your help and looking into my problem.

On Feb 26, 7:35 pm, "T. Valko" wrote:
So when we get past March 9th next month, the total will be 1.

You don't have any entries in your sample data for the month of March so
how
do you arrive at that result?

So when we get past March 9th

Why March 9th?

If you want to sum based on the *current* month:

=SUMPRODUCT(--(MONTH(A1:A100)=MONTH(NOW())),B1:B100)

--
Biff
Microsoft Excel MVP

"Steve" wrote in message

...

I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A B
01/01/09 3
02/01/09 2
19/01/09 7
09/02/09 3
18/02/09 1

TOTAL = 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

Can the formula not work on a rolling month? Say today the formula
would work from 27th Jan - 27th feb. Tomorrow would be the 28th and so
on.


On Feb 26, 9:06*pm, "T. Valko" wrote:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Steve" wrote in message

...
The result is from today. March 9th is an example, so when you get
past that date the data changes. The formula you have created works
fine, thanks for your help and looking into my problem.

On Feb 26, 7:35 pm, "T. Valko" wrote:

So when we get past March 9th next month, the total will be 1.

You don't have any entries in your sample data for the month of March so
how
do you arrive at that result?

So when we get past March 9th

Why March 9th?

If you want to sum based on the *current* month:

=SUMPRODUCT(--(MONTH(A1:A100)=MONTH(NOW())),B1:B100)

--
Biff
Microsoft Excel MVP

"Steve" wrote in message

....

I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A B
01/01/09 3
02/01/09 2
19/01/09 7
09/02/09 3
18/02/09 1

TOTAL = 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

It can, but you'd have to define what a month is.

For example the 29th to the 29th. What do you do for January 29 to February
29 when there are only 28 days in February?

What do you for May 31st to June 31st when June only has 30 days?

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Can the formula not work on a rolling month? Say today the formula
would work from 27th Jan - 27th feb. Tomorrow would be the 28th and so
on.


On Feb 26, 9:06 pm, "T. Valko" wrote:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Steve" wrote in message

...
The result is from today. March 9th is an example, so when you get
past that date the data changes. The formula you have created works
fine, thanks for your help and looking into my problem.

On Feb 26, 7:35 pm, "T. Valko" wrote:

So when we get past March 9th next month, the total will be 1.

You don't have any entries in your sample data for the month of March so
how
do you arrive at that result?

So when we get past March 9th

Why March 9th?

If you want to sum based on the *current* month:

=SUMPRODUCT(--(MONTH(A1:A100)=MONTH(NOW())),B1:B100)

--
Biff
Microsoft Excel MVP

"Steve" wrote in message

...

I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A B
01/01/09 3
02/01/09 2
19/01/09 7
09/02/09 3
18/02/09 1

TOTAL = 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

The rolling month would work off 28 days, I still can't get your
formula to work, the result is always 0.
Take this set of data,

A B
01/01/09 1
15/02/09 2
28/02/09 2

The formula would ignore the 01/01/09 (1 point) and sum up the 15th
and 28th feb, as its in the rolling month, which includes todays date
and anything 27 days previous.

On Feb 26, 8:06*pm, Mike H wrote:
Steve,

As Biff has pointed out your question lacks clarity. In your sample data
apart from Biff's point why isn't the answer 11? The last 3 dates are all
within 1 rolling month.

I also have some difficulty with the concept of rolling month, what is it?
30 days, 31 days, 29 days or even 28 days

Anyway, I'll propose a solution you can ponder on

=SUM(IF(A1:A100=LOOKUP(6.022*10^23,A1:A100)-30,B1:B100))

This array enterd formula takes the last date in column A and sums column B
for all dates within the 30 days previous of that.

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Steve" wrote:
I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A * * * * * * * * * * * * * * * * * B
01/01/09 * * * * * * * * * * * * 3
02/01/09 * * * * * * * * * * * * 2
19/01/09 * * * * * * * * * * * * 7
09/02/09 * * * * * * * * * * * * 3
18/02/09 * * * * * * * * * * * * 1

TOTAL = * * * * * * * * * * * * 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

Anybody got any ideas at all? I would be greatful if I could have a
little help on this. To get a rolling month formula.

On Mar 1, 6:25*pm, Steve wrote:
The rolling month would work off 28 days, I still can't get your
formula to work, the result is always 0.
Take this set of data,

A * * * * * * * * *B
01/01/09 * * * *1
15/02/09 * * * *2
28/02/09 * * * *2

The formula would ignore the 01/01/09 (1 point) and sum up the 15th
and 28th feb, as its in the rolling month, which includes todays date
and anything 27 days previous.

On Feb 26, 8:06*pm, Mike H wrote:

Steve,

As Biff has pointed out your question lacks clarity. In your sample data
apart from Biff's point why isn't the answer 11? The last 3 dates are all
within 1 rolling month.

I also have some difficulty with the concept of rolling month, what is it?
30 days, 31 days, 29 days or even 28 days

Anyway, I'll propose a solution you can ponder on

=SUM(IF(A1:A100=LOOKUP(6.022*10^23,A1:A100)-30,B1:B100))

This array enterd formula takes the last date in column A and sums column B
for all dates within the 30 days previous of that.

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Steve" wrote:
I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A * * * * * * * * * * * * * * * * * B
01/01/09 * * * * * * * * * * * * 3
02/01/09 * * * * * * * * * * * * 2
19/01/09 * * * * * * * * * * * * 7
09/02/09 * * * * * * * * * * * * 3
18/02/09 * * * * * * * * * * * * 1

TOTAL = * * * * * * * * * * * * 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

Try this:

=SUMPRODUCT(--(A1:A150=TODAY()-27),--(A1:A150<=TODAY()),B1:B150)

That works on a 28 day rolling period.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Anybody got any ideas at all? I would be greatful if I could have a
little help on this. To get a rolling month formula.

On Mar 1, 6:25 pm, Steve wrote:
The rolling month would work off 28 days, I still can't get your
formula to work, the result is always 0.
Take this set of data,

A B
01/01/09 1
15/02/09 2
28/02/09 2

The formula would ignore the 01/01/09 (1 point) and sum up the 15th
and 28th feb, as its in the rolling month, which includes todays date
and anything 27 days previous.

On Feb 26, 8:06 pm, Mike H wrote:

Steve,

As Biff has pointed out your question lacks clarity. In your sample data
apart from Biff's point why isn't the answer 11? The last 3 dates are
all
within 1 rolling month.

I also have some difficulty with the concept of rolling month, what is
it?
30 days, 31 days, 29 days or even 28 days

Anyway, I'll propose a solution you can ponder on

=SUM(IF(A1:A100=LOOKUP(6.022*10^23,A1:A100)-30,B1:B100))

This array enterd formula takes the last date in column A and sums
column B
for all dates within the 30 days previous of that.

'This is an array formula which must be entered with CTRL+Shift+Enter
and NOT
'just enter. If you do it correctly then Excel will put curly brackets
around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Steve" wrote:
I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A B
01/01/09 3
02/01/09 2
19/01/09 7
09/02/09 3
18/02/09 1

TOTAL = 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

Great! thanks for all your help both formulas are very useful.

On Mar 3, 2:38*am, "T. Valko" wrote:
Try this:

=SUMPRODUCT(--(A1:A150=TODAY()-27),--(A1:A150<=TODAY()),B1:B150)

That works on a 28 day rolling period.

--
Biff
Microsoft Excel MVP

"Steve" wrote in message

...
Anybody got any ideas at all? I would be greatful if I could have a
little help on this. To get a rolling month formula.

On Mar 1, 6:25 pm, Steve wrote:

The rolling month would work off 28 days, I still can't get your
formula to work, the result is always 0.
Take this set of data,

A B
01/01/09 1
15/02/09 2
28/02/09 2

The formula would ignore the 01/01/09 (1 point) and sum up the 15th
and 28th feb, as its in the rolling month, which includes todays date
and anything 27 days previous.

On Feb 26, 8:06 pm, Mike H wrote:

Steve,

As Biff has pointed out your question lacks clarity. In your sample data
apart from Biff's point why isn't the answer 11? The last 3 dates are
all
within 1 rolling month.

I also have some difficulty with the concept of rolling month, what is
it?
30 days, 31 days, 29 days or even 28 days

Anyway, I'll propose a solution you can ponder on

=SUM(IF(A1:A100=LOOKUP(6.022*10^23,A1:A100)-30,B1:B100))

This array enterd formula takes the last date in column A and sums
column B
for all dates within the 30 days previous of that.

'This is an array formula which must be entered with CTRL+Shift+Enter
and NOT
'just enter. If you do it correctly then Excel will put curly brackets
around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Steve" wrote:
I have a sheet of which I will be inputted data onto every so often..
The worksheet has only two columns, date and points. I want the sheet
to automatically workout the amount of points in a rolling month.

A B
01/01/09 3
02/01/09 2
19/01/09 7
09/02/09 3
18/02/09 1

TOTAL = 4

So when we get past March 9th next month, the total will be 1. As this
is the months total of points. Can someone please help?

Steve

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Great! thanks for all your help both formulas are very useful.

On Mar 3, 2:38 am, "T. Valko" wrote:
Try this:

=SUMPRODUCT(--(A1:A150=TODAY()-27),--(A1:A150<=TODAY()),B1:B150)

That works on a 28 day rolling period.

--
Biff
Microsoft Excel MVP

"Steve" wrote in message

...
Anybody got any ideas at all? I would be greatful if I could have a
little help on this. To get a rolling month formula.

On Mar 1, 6:25 pm, Steve wrote:

The rolling month would work off 28 days, I still can't get your
formula to work, the result is always 0.
Take this set of data,

A B
01/01/09 1
15/02/09 2
28/02/09 2

The formula would ignore the 01/01/09 (1 point) and sum up the 15th
and 28th feb, as its in the rolling month, which includes todays date
and anything 27 days previous.

On Feb 26, 8:06 pm, Mike H wrote:

Steve,

As Biff has pointed out your question lacks clarity. In your sample
data
apart from Biff's point why isn't the answer 11? The last 3 dates are
all
within 1 rolling month.

I also have some difficulty with the concept of rolling month, what is
it?
30 days, 31 days, 29 days or even 28 days

Anyway, I'll propose a solution you can ponder on

=SUM(IF(A1:A100=LOOKUP(6.022*10^23,A1:A100)-30,B1:B100))

This array enterd formula takes the last date in column A and sums
column B
for all dates within the 30 days previous of that.

'This is an array formula which must be entered with CTRL+Shift+Enter
and NOT
'just enter. If you do it correctly then Excel will put curly brackets
around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Steve" wrote:
I have a sheet of which I will be inputted data onto every so often.
The worksheet has only two columns, date and points. I want the
sheet
to automatically workout the amount of points in a rolling month.

A B
01/01/09 3
02/01/09 2
19/01/09 7
09/02/09 3
18/02/09 1

TOTAL = 4

So when we get past March 9th next month, the total will be 1. As
this
is the months total of points. Can someone please help?

Steve