Countif for dates between (xp)

Topic

New Reply

I have a column which contains dates, I want to use the countif function to count the dates in a certain month. I can’t seem to get it working right. Can somebody suggest something

Reply | Quote

Replies

If the dates are in A1:A25, then the following array formula (hold down Shift+ Ctrl when you enter it) will count the number that are in January:

=SUM((MONTH(A1:A25)=1)*(A1:A25""))

To count February, change the “=1” to “=2”, or change the “=1” to “=” and a cell reference where you will enter the month number.

Reply | Quote

I can’t help if you are committed to using “countif” – but if you are willing to use an array function, this will work:

Assuming your column of dates is in column A, extending from A2 to A78, and you want to be able to count the number that occur in May, June, November, etc – place a number from 1-12, representing the month (January – December) in cell A80. In cell B80, enter the formula:

=SUM(IF(MONTH(A$2:A$78) = A80,1,0))

This will be an array formula, and must be entered by hitting ctrl-shift-enter. Excel will insert braces around the formula to indicate that it is an array formula. If you need to see the count for more than one month at a time, drag to copy the formula down the B column, and then put in the months you need. If required, you can have twelve copies of the formula and show the count for all twelve months at once. The referenced range for the formula (A2:A78) can be changed either by editing the formula bar or by dragging an outline – just remember to enter the formula by ctrl-shift-enter.

An alternate formula woudl be:

=SUM((MONTH(A$2:A$78) = A81)*1)

This might evaluate faster, but I doubt that the difference would be noticeable

If you are dealing with more than one year you can edit the criteria to use an “AND” function to test for the value in the range to be more than a particular starting date and less than an ending date, as well – unless you want to aggregate May 02 with May 01

Reply | Quote

As well as the other excellent suggestions, you can use two countifs like this:

=COUNTIF(daterange,”>”&DATE(year,month,0))-COUNTIF(daterange,”>”&DATE(year,month + 1,0))

Reply | Quote