How to count dates only contains one month?

Occasional Visitor

How to count dates only contains one month?

I have a lot of dates in my excel from January till December and i want to know how many dates where from April? I tried with Countif but “apr” or “-4-“ doesnt work.

3 Replies

Re: How to count dates only contains one month?

For example:

=COUNTIFS(X26:AO49; ">="&DATE(2022; 4; 1); X26:AO49; "<="&DATE(2022; 4; 30))

Re: How to count dates only contains one month?

@Hans Vogelaar solution is probably the better, but it is possible to use month numbers or even names.

``````"Based on Hans's solution"
= COUNTIFS(
dateList, ">="&EDATE(startDate,3),
dateList, "<="&EOMONTH(startDate,3)
)

"Array formula using SUM"
= SUM(
--(MONTH(dateList)=4)
)``````

COUNTIFS is a very fast function.  The SUM formula will count April dates irrespective of their year.

Re: How to count dates only contains one month?

Where possible I'd avoid CSE in pre-DA Excel using functions which natively work with arrays. In this case

``=SUMPRODUCT( (MONTH(datelist)=4)*(year(datelist)=2022) )``

Performance is the point, but is that our case?