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. It can also count the cells in a month and day, e.g., a person's birthday on June 22.
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 on June 22?
=SUMPRODUCT(–(MONTH(C2:C12)=6), –(DAY(C2:C12)=22))
- Step 1: (MONTH(C2:C12)=6): if DOB is in June, it returns true, otherwise false;
- Step 2: ( DAY(C2:C12)=22): if DOB is on 22, it returns true, otherwise false;
- Step 3: The double hyphens (–) convert true into one and false into zero;
- Step 4: The SUMPRODUCT returns the sum of the products of the array.
The result returns 3, so three people were born on June 22.
Formula 2: How many people were born on November 21?
=SUMPRODUCT(–(MONTH(C2:C12)=11), –(DAY(C2:C12)=21))
The result returns 2, so two people were born on November 21.
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.