COUNTIF-look for dates in range

[richzip]

I have a spreadsheet where there are several cells, each containing a date.
I would like to set up a formula that counts how many of thsoe cells contain
dates within each month. So, I want to count the number of cells that
contain dates from 1/1-1/31, 2/1-2/28, etc.

 

[Pete_UK]

You could do it like this:

=COUNTIF(D,">="&E1)-COUNTIF(D">"&F1)

where I've assumed column D contains your dates, and that you put your
start date in cell E1 and the finish date in cell F1. If you put the
formula in G1, then you can copy it down with other dates in E2:F2
etc.

Hope this helps.

Pete

 

[bapeltzer]

My preference is to use two countif functions for each range. For example,
to count the dates in January 2009, I'd count all those on or after Jan 1 09
and subtract the count of those on or after Feb 1 09:
=countif(range,">=" & date(2009,1,1)) - countif(range,">=" & date(2009,2,1))

 

[Shane Devenshire]

Hi,

Try this:

=SUMPRODUCT(--(MONTH(A$1:A$200)=D1))

where D1 contains 1 for January and your dates are in A1:A200. Note that
this is not year specific - that means it will count the number of January
dates in any year.
You can enter the numbers 1-12 in D112 and copy the formula down.

 

[Shane Devenshire]

Hi,

If you need to handle years then

=SUMPRODUCT(--(TEXT(A$1:A$200,"MY")=G1))

In this case you enter your date in as text in G1 as 109 meaning Jan 09.
You can enter the date as text you typing '109 (putting an apostrophy in
front of the number.

There are many variations of this idea depending on how you want to indicate
the date.