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 after a specific day, e.g., the number of people that were born after 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 after May 2, 1992
=COUNTIF(C2:C12,">5/2/1992")
The result returns 6. You can also use the SUMPRODUCT function.
Formula 2: To count the number of people that were born after May 2, 1992
=SUMPRODUCT(–(C2:C12>DATE(1992,5,2)))
=SUMPRODUCT((C2:C12>DATE(1992,5,2))*1)
- DATE(1992,5,2): It is the way to write a date in the formula, which can be changed into a reference cell (e.g., C8);
- (C2:C12>DATE(1992,5,2)): if DOB is after May 2, 1992, it returns true, otherwise false;
- The double hyphens (or times 1 in the second formula) convert true into 1, and false into 0;
- The SUMPRODUCT returns the sum of the products of the array.
The result returns 6, which means that six people were born after May 2, 1922.
Formula 3: To count the number of people that were born after May 2, 1992
=SUMPRODUCT(–(C2:C12>C8))
=SUMPRODUCT((C2:C12>C8)*1)
In many situations, we do not need to include the date in the formula. Instead, we use the reference cell. For example, in the question, cell C8 has the date of May 2, 1992.
The result returns 6, which means that six people were born after May 2, 1922.
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.
