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 between two dates.
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 between Jan 1, 1992, and Dec 31, 1993
=COUNTIFS(C2:C12,">1/1/1992",C2:C12,"<12/31/1993")
The result returns 4. You can also use the SUMPRODUCT function.
Formula 2: To count the number of people that were born between Jan 1, 1992, and Dec 31, 1993
=SUMPRODUCT(–(C2:C12>DATE(1992,1,1)),–(C2:C12<DATE(1993,12,31)))
=SUMPRODUCT((C2:C12>DATE(1992,1,1))*1,(C2:C12<DATE(1993,12,31))*1)
- –(C2:C12>DATE(1992,1,1)): It is the way to write a date in the formula, which can be changed into a reference cell (e.g., C8);
- 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 between Jan 1, 1992, and Dec 31, 1993
Formula 3: To count the number of people that were born between Jan 2, 1992, and Dec 31, 1993
=SUMPRODUCT(–(C2:C12>C9),–(C2:C12<DATE(1993,12,31)))
=SUMPRODUCT((C2:C12>C9)*1,(C2:C12<DATE(1993,12,31))*1)
In many situations, we do not need to include the date in the formula. Instead, we use the reference cells. For example, in the question, cell C9 has the date of Jan 2, 1992.
The result returns 3, which means that three people were born between Jan 2, 1992, and Dec 31, 1993.
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.