The SUMPRODUCT function is to return the sum of the products of corresponding ranges or arrays. It can also count the cells in a certain year, a month, or a day. The following formula is to determine the number of cells in a year.
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: How many people were born in 1992?
=SUMPRODUCT(–(YEAR(C2:C12)=1992))
- Step 1: YEAR(C2:C12)=1992: if DOB was in 1992, it returns true, otherwise false;
- Step 2: The double hyphens (–) convert true into one and false into zero;
- Step 3: The SUMPRODUCT returns the sum of the products of the array.
The result returns 3, so three people were born in 1992.
Alternatively, you can also use the COUNTIFS function.
Formula 2: How many people were born in 1994?
=COUNTIFS(C2:C12,">=1/1/1994",C2:C12,"<=12/31/1994")
The result returns 2, so two people were born in 1994.
Formula 3: If the dates are in the cells (for example, G1 is the start date, and H1 is the end date), you can use the following formula:
=COUNTIFS(C2:C12,">="&G1,C2:C12,"<="&H1)
The result returns 2, so two people were born in 1994.
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 add.
– Array2 is optional; the second array is to multiply and add.