How to count dates only contains one month?

Occasional Visitor

6505649F-B1EC-40F6-A282-A17D450E7DAA.jpeg

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

@ilsjx 

For example:

 

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

@ilsjx 

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

@Peter Bartholomew 

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?