COUNTIF Date – How To Count Cells Based on Year, Month or Day with the Aid of a Helper Column

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):

COUNTIF(range,criteria)

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)…

Graphic 1

  1. Add a column to retrieve the year, month or day as needed
  2. Add column B as shown in graphic 1 above.

  3. Extract the Year, Month Or Day with the corresponding Excel Function YEAR, MONTH, DAY
  4. 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)

  5. Apply COUNTIF with the criteria contained in the helper column
  6. 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