The SUMPRODUCT function is to return the sum of the products of corresponding ranges or arrays. It can sum the numbers in a certain year, a month, or a day. The following example is to sum numbers on a month and day, e.g., November 21.
Example: You are working with a dataset with the product name in column A, the date sold in column B, and the amount sold in column C.
Formula 1: To count the total number of products sold on June 22
=SUMPRODUCT(–(MONTH(B2:B12)=6),–(DAY(B2:B12)=22),C2:C12)
(MONTH(B2:B12)=6): if the month is June, it returns true, otherwise false;
(DAY(B2:B12)=22): if the day is 22, 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 4,814, and 4,814 products were sold on June 22.
Formula 2: To count the total number of products sold on November 21
=SUMPRODUCT(–(MONTH(B2:B12)=11),–(DAY(B2:B12)=21),C2:C12)
The result returns 4,065, and 4,065 products were sold 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 then add.
– Array2 is optional, the second array is to multiply and then add.