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 day (e.g., the ninth day in a month).
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 on the second day
- DAY(C2:C12)=2: if DOB is on the second, it returns true, otherwise false;
- The double hyphens (--) convert true into 1, and false into 0;
- The SUMPRODUCT returns the sum of the products of the array.
The result returns 3, and three people were born on the second day.
Formula 2: To count the number of people that were born on the 10th
The result returns 2, and two people were born on the 10th.
Notes: The SUMPRODUCT function
The SUMPRODUCT function adds all the multiplication results for all arrays.
=SUMPRODUCT(array1, [array2], …)
– Array1 is required, the first array is to multiply and then add.
– Array2 is optional, the second array is to multiply and then add.