|
Totals
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 |
Totals
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 |
Totals
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 |
Totals
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 |
Totals
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 |
Totals
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 |
Totals
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 |
Totals
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 |
Totals
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 |
Totals
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 |
Totals
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 |
Totals
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 |
| All times are GMT +1. The time now is 06:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com