How to Count Cells on A Specific Day

The COUNTIF function is to count the number of cells that meet one criterion such as the cells with a text string, and the cells with a certain value, etc. It can also count the cells on a specific day, e.g., a person's appointment on June 22, 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: To count the number of people that were born on June 22, 1993

=COUNTIF(C2:C12, "6/22/1993")

The result returns 2, and two people were born on June 22. You can use the reference cell if the date is in a cell (e.g., E1)

=COUNTIF(C2:C12, "="&E1)

Other than the COUNTIF function, you can also use the PRODUCT function.

Formula 2: To count the number of people that were born on June 22, 1993

=SUMPRODUCT(--(MONTH(C2:C12)=6), --(DAY(C2:C12)=22), --(YEAR(C2:C12)=1993))

  • (MONTH(C2:C12)=6): if DOB is in June, it returns true, otherwise false;
  • ( DAY(C2:C12)=22): if DOB is on 22, it returns true, otherwise false;
  • (YEAR(C2:C12)=1993): if DOB was born in 1993.
  • The double hyphens (--) convert true into 1, and false into 0;
  • The SUMPRODUCT returns the sum of the products of the array.

The result returns 2, and two people were born on June 22.

Formula 3: To count the number of people that were born on November 21, 1998

=SUMPRODUCT(--(MONTH(C2:C12)=6), --(DAY(C2:C12)=22), --(YEAR(C2:C12)=1993))

The result returns 2, and two people were born on November 21

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 then add.
– Array2 is optional, the second array is to multiply and then add.

Leave a Reply