Rolling 12 Months Stats

[Chris Waller]

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





[TABLE="width: 895"]
<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>[TR]
[TD="width: 128, bgcolor: transparent"][/TD]
[TD="width: 98, bgcolor: transparent, align: right"]Nov-13[/TD]
[TD="width: 89, bgcolor: transparent, align: right"]Dec-13[/TD]
[TD="width: 88, bgcolor: transparent, align: right"]Jan-14[/TD]
[TD="width: 88, bgcolor: transparent, align: right"]Feb-14[/TD]
[TD="width: 87, bgcolor: transparent, align: right"]Mar-14[/TD]
[TD="width: 89, bgcolor: transparent, align: right"]Apr-14[/TD]
[TD="width: 89, bgcolor: transparent, align: right"]May-14[/TD]
[TD="width: 84, bgcolor: transparent, align: right"]Jun-14[/TD]
[TD="width: 88, bgcolor: transparent, align: right"]Jul-14[/TD]
[TD="width: 88, bgcolor: transparent, align: right"]Aug-14[/TD]
[TD="width: 89, bgcolor: transparent, align: right"]Sep-14[/TD]
[TD="width: 87, bgcolor: transparent, align: right"]Oct-14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BF[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New
[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Carried Forward
[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

 

[RickXL]

Hi,

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

 

[Chris Waller]

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]

I can get you started.

[TABLE="width: 803"]
<tbody>[TR]
[TD][TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nov-13[/TD]
[TD]Dec-13[/TD]
[TD]Jan-14[/TD]
[TD]Feb-14[/TD]
[TD]Mar-14[/TD]
[TD]Apr-14[/TD]
[TD]May-14[/TD]
[TD]Jun-14[/TD]
[TD]Jul-14[/TD]
[TD]Aug-14[/TD]
[TD]Sep-14[/TD]
[TD]Oct-14[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]New[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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]

FAO RickXL,

Thanks for that it has given me a good start.

 

[RickXL]

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]

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]

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,"="&"")

 

[Chris Waller]

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]

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