How to Sum Numbers on A Day

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 day.

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 the 2nd

=SUMPRODUCT( --(DAY(B2:B12)=2),C2:C12)

(DAY(B2:B12)=2): if the day is on the 2nd of the month, 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,001, and 3,001 products were sold on the 2nd.

Formula 2: To count the total number of products sold on the 22nd

=SUMPRODUCT( --(DAY(B2:B12)=22),C2:C12)

The result returns 5,896, and 5,896 products were sold on the 22nd.

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.

Leave a Reply