How to Count Cells in A Month of a Year

The SUMPRODUCT function is to return the sum of the products of corresponding ranges or arrays. It can also count the cells in a certain yeara month, or a day. It can also count the cells in a month and year (e.g., October 2020).

Example: You are working with a dataset with the first name in column A, the last name in column B, and the DOB (date of birth) in column C.

Formula 1: How many people were born in November 1998?

=COUNTIFS(C2:C12,">=11/1/1998",C2:C12,"<=11/30/1998")

The result returns 2, so two people were born in November 1998.

Alternatively, you can use the SUMPRODUCT function

Formula 2: How many people were born in November 1998?

=SUMPRODUCT(–(MONTH(C2:C12)=11), –(YEAR(C2:C12)=1998))

  • Step 1: (MONTH(C2:C12)=11): if DOB is in November, it returns true, otherwise false;
  • Step 2: (YEAR(C2:C12)=1998): if DOB was in 1998, it returns true, otherwise false;
  • Step 3: The double hyphens (–) convert true into one and false into zero;
  • Step 4: The SUMPRODUCT returns the sum of the products of the array.

The result returns 2, so two people were born in November 1998.

Formula 3: How many people were born in June 1990?

=SUMPRODUCT(–(MONTH(C2:C12)=6), –(YEAR(C2:C12)=1990))

The result returns 3, so three people were born in June 1990.

Notes: The SUMPRODUCT function

The SUMPRODUCT function adds all the multiplication results for all arrays.

Formula:

=SUMPRODUCT(array1, [array2], …)

Explanations:

– Array1 is required; the first array is to multiply and add.
– Array2 is optional; the second array is to multiply and add.

Leave a Reply