COUNTIF function itself is not capable of analyzing year, month or day from a serial number; then, how to use it to count dates based on these criteria?
The answer is: you can use several methods (SUMPRODUCT, COUNTIFS, Array Formulas); here you will use a plain Excel COUNTIF function through the use of a helper column.
The key for generating a COUNTIF Date is creating a criteria-column instead of focusing on complex date criterion to be coded in “criteria” argument (see syntax below):
Helper columns expand your analysis capacity. For example, you can:
- Turn SUMIF, COUNTIF into multiple criteria versions
- Perform complex queries based on additional fields with Filter command
- Sort by other parameters as TRUE/FALSE, messages from IF function, etc
- Group in granular ways with Pivot Table
See below this method in action (see graphic 1)…
- Add a column to retrieve the year, month or day as needed
- Extract the Year, Month Or Day with the corresponding Excel Function YEAR, MONTH, DAY
- Apply COUNTIF with the criteria contained in the helper column
Add column B as shown in graphic 1 above.
In this example, the column B extract the year from column A with this formula: =YEAR(A2)
Here you have the two other possible formulas: =MONTH(A2) and =DAY(A2)
The formula is as simple as this one: =COUNTIF(B2:B22,2005)
To not hard code: =COUNTIF(B2:B22,I2)
Where I2 is the year