Rolling 12 Months Stats

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
174
Hello,

I wonder if anyone can help e create a rolling 12 months stats table like the one below:
The date in the October 2014 column is created using the =now() formula which has been formatted to MMM-YYYY. In the columns for the preceding 11 months I have used the EOMONTH formula again formatted to MMM-YYYY. Every time we move to a new month the month name moves one column to the left. Ideally what I would like to do beneath each month is to create a formula that will pick up the corresponding information for that month. For example. Where any data is input on the spreadsheet column A for example for October 2014 the formula will count all the information input from the first day of the moth to the last day of the month. Similarly, the BF row must contain ALL the information that has been registered, but there is no date in the completed column.

The information on the Carried Forward row is the same information as the BF row, but this includes all the information recorded for that month, but still does not have a date in the completed column. I hope I have explained clearly enough for you to get your head around the information, if not please post back and I would be more than willing to help clarify any point that is not clear. TIA





Nov-13Dec-13Jan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14Sep-14Oct-14
BF122222233333
New
1111111111123
Carried Forward
222222333337
<colgroup><col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 3640;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 2787;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 2531;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 2503;" span="2"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 2474;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 2531;" span="2"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 2389;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 2503;" span="2"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 2531;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 2474;"> <tbody> </tbody>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Hi,

It would help me if I knew what the input data looked like.
 

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
174
The information is in a column headed "Date received by " and "Impact received by". These two columns just contain dates in the dd/mm/yyyy format. The first is the date the item was received on the team and the second is the date that the item should be returned to the team. As you can imagine this does not always happen, so we need a count of ALL items (dates within the month) and all items which are still outstanding. HTH
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
I can get you started.

ABCDEFGHIJKLMNO
1Nov-13Dec-13Jan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14Sep-14Oct-14
2New000000033543

<tbody>
</tbody>

<tbody>
</tbody>
D1 is =EDATE(EOMONTH(TODAY(),0)+1,-12)
E1 is =EDATE(D2,1)
F1 to O1 is E1 dragged across
D2 is =COUNTIFS($A:$A,">=" & D2,$A:$A,"<=" & EOMONTH(D2,0))
E2 to O2 is D2 dragged across

My data was in columns A and B.
 

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
174

ADVERTISEMENT

FAO RickXL,

Thanks for that it has given me a good start.
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
No problem.

I would have done more but I really don't understand the problem sufficiently.

I am hoping that by putting some slightly more complex formulas into the COUNTIFS you should be able to do what you want.
 

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
174

ADVERTISEMENT

RickXL,

The formula that you have given, partially works. For the line containing new items it does the job, however the BF line and the Carried Forward line do not work quite so well.

The data on the BF and the Carried forward line is more or less the same. The BF line is a count of all those items that contain a date in column A but do not contain a date in column B. This is a count prior to the start of the month in question.

The data on the Carried forward line is exactly the same, just that it is a count on the last day of the month. HTH
The data
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
OK.

If I have understood correctly, then

New will be =COUNTIFS($A:$A,">=" & D$2,$A:$A,"<=" & EOMONTH(D$2,0))
BF will be =COUNTIFS($A:$A,"<" & D$2,$B:$B,"="&"")
CF will be =COUNTIFS($A:$A,"<" & EOMONTH(D$2,0),$B:$B,"="&"")
 
Last edited:

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
174
RickXL,

Kudos to you. That worked a treat. Thanks for your help with this. I wich I could get my head around creating formulas. Do you have the titles of any books I could buy to increase my knowledg of formula writing?
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Thank you.

I am sorry, I don't know any Excel books. I get most of my information from the internet via Google.
This site is obviously useful, I have also looked at the ozgrid site and Chip Pearson's site.
Chandoo and Jo Peltier are also worth a look. A Google search on something like:
Excel SumIf
will find lots of examples of how to use SumIf, for instance.

The main thing with a language is to learn the vocabulary. So make a list of the useful functions like INDEX & MATCH, COUNT, COUNTIF, COUNTIFS, AVERAGE, SUM, etc then find out what you can do with them. The date functions are also essentials.

I expand my knowledge by coming here and trying to solve other people's problems :)
 

Forum statistics

Threads
1,144,376
Messages
5,724,001
Members
422,530
Latest member
Badpoisondwarf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top