Totals

[Steve]

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

 

[T. Valko]

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)

 

[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.

 

[Mike H]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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.

 

[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.

 

[T. Valko]

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


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.

 

[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.

 

[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.

 

[T. Valko]

Try this:

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

That works on a 28 day rolling period.

--
Biff
Microsoft Excel MVP


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.

 

[Steve]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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