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:

16/04/2014

18/02/2013

31/01/2014

04/02/2015

15/09/2014

03/01/2013

11/02/2015

27/08/2014

01/10/2015

12/04/2011

24/06/2015

00/01/1900

27/02/2015 13/10/2015

<colgroup><col></colgroup><tbody>
</tbody>

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

Column A	Column B
Sydney	14/10/2015
Melbourne	01/01/2015
Brisbane	06/07/2015
Sydney	06/06/2014
Sydney	07/03/2015
Brisbane	04/05/2014
Melbourne	05/08/2013
Melbourne	09/06/2015
Adelaide	08/08/2015

<colgroup><col><col></colgroup><tbody>
</tbody>

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.