How to Count Cells in the Current Month

To count the number of cells in the current month, you can use the COUNTIFS or SUMPRODUCT functions.

Example: You are working with a worksheet with the first name in column A, the last name in column B, and the date of appointment in column C.

Question: How many people have appointments in the current month?

=COUNTIFS(C2:C12, ">=" & (EOMONTH(TODAY(),-1)+1), C2:C12, "<=" & (EOMONTH(TODAY(),0)))

The result returns 2, so there are two appointments in the current month if the current month is November 2022.

Explanation:

Step 1: TODAY(): To have today's date;
Step 2: (EOMONTH(TODAY(),-1)+1): The result returns the first day of the current month;
Step 3: (EOMONTH(TODAY(),0)): The result returns the last day of the current month;

Alternatively, you can use the SUMPRODUCT function to have the same results.

=SUMPRODUCT(–(C2:C12 >= (EOMONTH(TODAY(),-1)+1)), –(C2:C12 <= (EOMONTH(TODAY(),0))))

=SUMPRODUCT((C2:C12 >= (EOMONTH(TODAY(),-1)+1))*1, (C2:C12 <= (EOMONTH(TODAY(),0)))*1)

Explanation:

Step 1: TODAY(): To have today's date;
Step 2: (EOMONTH(TODAY(),-1)+1): The result returns the first day of the current month;
Step 3: (EOMONTH(TODAY(),0)): The result returns the last day of the current month;
Step 5: (C2:C12 >= (EOMONTH(TODAY(),-1)+1)): The result returns TRUE if a date is after the first day of the current month;
Step 6: (C2:C12 <= (EOMONTH(TODAY(),0))): The result returns TRUE if the date is before the last of the current month;
Step 7: The hyphen (or times one) converts TRUE into one and FALSE into zero;
Step 8: The SUMPRODUCT function returns the sum of the products of the new array.

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.

ExcelNotes