Countif formula within 2 date ranges

[da_vide78]

hi there.


I've trawled the site and web but can't find anything that works.


I have a table of data with each line having a start and end date. I need to work out how many activities happen between 2 set end dates. Below is a sample.


A B C
1 Activity Start End
2 Activity 1 21/3/11 23/3/11
3 Activity 2 25/3/11 30/3/11
4 Activity 3 2/4/11 04/04/11
5 Activity 4 5/4/11 06/04/11
6 Activity 5 5/4/11 08/04/11

I can't seem to make my countif formula take into account a 'greater than' and 'smaller than' request.

Any help would be appreciated?

Cheers

 

[wigi]

As an example:

=COUNTIF("B2:B6","<="&D1)

I assume cell D1 holds the date with which you want to compare.
You can extend the logic to also have a test on >= for example.

 

[jasonb75]

It depends which version of excel.

2007 or later =COUNTIFS(B2:B6,">"& start date,B2:B6,"<"& end date)

2003 or older

= COUNTIF(B2:B6,"<"& end date)-COUNTIF(B2:B6,"<="& start date)