CountIf Function for Variable Date Range

[ShaunF]

Hi,

Am after a simple formula for counting the numbers of instances a date falls in the previous six months.

For example, I may have data that shows:

[TABLE="width: 119"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]16/04/2014[/TD]
[/TR]
[TR]
[TD="align: right"]18/02/2013[/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2014[/TD]
[/TR]
[TR]
[TD="align: right"]04/02/2015[/TD]
[/TR]
[TR]
[TD="align: right"]15/09/2014[/TD]
[/TR]
[TR]
[TD="align: right"]03/01/2013[/TD]
[/TR]
[TR]
[TD="align: right"]11/02/2015[/TD]
[/TR]
[TR]
[TD="align: right"]27/08/2014[/TD]
[/TR]
[TR]
[TD="align: right"]01/10/2015[/TD]
[/TR]
[TR]
[TD="align: right"]12/04/2011[/TD]
[/TR]
[TR]
[TD="align: right"]24/06/2015[/TD]
[/TR]
[TR]
[TD="align: right"]00/01/1900[/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2015
13/10/2015[/TD]
[/TR]
</tbody>[/TABLE]

I want to count the number of times a date appears in the previous six months (from date of running the report). Assuming I ran the report on 13/10/2015, it should return a result of '3'.

Any assistance appreciated.

Thanks,

Shaun.

 

[dreid1011]

Perhaps this:

=COUNTIFS(A1:A14,">="&EDATE(B1,-6),A1:A14,"<="&B1)

Where B1 contains the date 13/10/2015.

 

[ShaunF]

Hi Dreid1011,

Thanks for the suggestion. So my full formula is the below, however is returning a result of 0 which is not correct. Any ideas on where I may have gone wrong?

=(SUMPRODUCT(COUNTIFS(RJCP01!$T$2:$T$2000,KNX,RJCP01!$BS$2:$BS$2000,">="&EDATE(CI1,-6),RJCP01!$BS$2:$BS$2000,"<=CI1")))

Regards,

Shaun.

 

[dreid1011]

At the very least, make the change highlighted below:

=(SUMPRODUCT(COUNTIFS(RJCP01!$T$2:$T$2000,KNX,RJCP01!$BS$2:$BS$2000,">="&EDATE(CI1,-6),RJCP01!$BS$2:$BS$2000,"<=CI1")))

to

=(SUMPRODUCT(COUNTIFS(RJCP01!$T$2:$T$2000,KNX,RJCP01!$BS$2:$BS$2000,">="&EDATE(CI1,-6),RJCP01!$BS$2:$BS$2000,"<="&CI1)))

 

[ShaunF]

Sorry though still does not appear to work.

=(SUMPRODUCT(COUNTIFS(RJCP01!$T$2:$T$2000,KNX,RJCP01!$BS$2:$BS$2000,">="&EDATE(CI1,-6),RJCP01!$BS$2:$BS$2000,"<="&CI1)))

Thanks.

 

[dreid1011]

What is in RJCP01!$T$2:$T$2000 and KNX?

 

[ShaunF]

Column T represents locations. There are around 170 locations grouped into 7 clusters. KNX represents one of the clusters.

So in essence, as a further example, when using the below data I am wanting to return the number of times a date occurs in Brisbane which falls in the previous 6 months. In this case '1'.

[TABLE="width: 170"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD="align: right"]14/10/2015[/TD]
[/TR]
[TR]
[TD]Melbourne[/TD]
[TD="align: right"]01/01/2015[/TD]
[/TR]
[TR]
[TD]Brisbane[/TD]
[TD="align: right"]06/07/2015[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD="align: right"]06/06/2014[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD="align: right"]07/03/2015[/TD]
[/TR]
[TR]
[TD]Brisbane[/TD]
[TD="align: right"]04/05/2014[/TD]
[/TR]
[TR]
[TD]Melbourne[/TD]
[TD="align: right"]05/08/2013[/TD]
[/TR]
[TR]
[TD]Melbourne[/TD]
[TD="align: right"]09/06/2015[/TD]
[/TR]
[TR]
[TD]Adelaide[/TD]
[TD="align: right"]08/08/2015[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,

Shaun.

 

[dreid1011]

You should be able to do this without the SUMPRODUCT wrapped around your COUNTIFS.

Both of these worked for me:

=COUNTIFS(A1:A9,D2,B1:B9,">="&EDATE(D1,-6),B1:B9,"<="&D1)
=SUMPRODUCT((A1:A9=D2)*(B1:B9>=EDATE(D1,-6))*(B1:B9<=D1))

Where:
A1:A9 = Locations
B1:B9 = Dates
D1 = Target Date (13/10/2015)
D2 = Target Location (Brisbane)

Result = 1

 

[ShaunF]

Thanks for your support. When using the exact example above I get a result of 0, instead of 1.

When using actual data I also get a result of 0.

Frustrating. Any ideas?

 

[dreid1011]

I'm not sure. I had to changes your dates from dd/mm/yyyy to mm/dd/yyyy being on an American system. Other than that, the only thing I can think of is they are not recognized as actual dates.


I apologize for the delay in response, I was on vacation.