Site icon ExcelNotes

How to Count Cells in A Month

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))

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.

Exit mobile version