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 before a specific day, e.g., the number of people that were born before 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 before May 2, 1992
=COUNTIF(C2:C12,"<5/2/1992")
Four people were born before May 2, 1922. You can replace the data (5/2/1992) with the reference cell if the date is in a cell (e.g., E1)
=COUNTIF(C2:C12,"<"&E1)
You can also use the SUMPRODUCT function.
Formula 2: To count the number of people that were born before 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 earlier than 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 4, which means that four people were born before May 2, 1922.
Formula 3: To count the number of people that were born before 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 4, which means that four people were born before 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.