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 figure out the number of cells in a month (e.g., June).
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 in June
=SUMPRODUCT(–(MONTH(C2:C12)=6))
- Step 1: MONTH(C2:C12)=6: if DOB is in June, 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 2, and two people were born in June.
Formula 2: To count the number of people that were born in November
=SUMPRODUCT(–(MONTH(C2:C12)=11))
The result returns 3, and three 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.