Totals

S

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

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)
 
S

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

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

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

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

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

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

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

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

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top