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.
