How to Count Cells in the Last Month

You can use the COUNTIFS or SUMPRODUCT functions to count the number of cells in the last month.

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 had appointments in the last month?

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

The result returns 3, so there are three appointments in the last month, if today is November 15, 2022.

Explanation:

Step 1: TODAY(): To have today's date;
Step 2: EOMONTH(TODAY(), -2): The formula returns the last day of the two months ago;
Step 3: (EOMONTH(TODAY(), -2) + 1): The result returns the first day of last month;
Step 4: EOMONTH(TODAY(), -1): The result returns on the last day of the previous month.

You can also use the SUMPRODUCT function to have the same results.

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

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

Explanation:

Step 1: TODAY(): To have today's date;
Step 2: EOMONTH(TODAY(), -2): The formula returns the last day of the two months before;
Step 3: (EOMONTH(TODAY(), -2) + 1): The result returns the first day of last month;
Step 4: EOMONTH(TODAY(), -1): The result returns the last day of the last month;
Step 5: (C2:C12 >= (EOMONTH(TODAY(), -2) + 1)): The result returns TRUE if a date is after the first day of the last month;
Step 6: (C2:C12 <= (EOMONTH(TODAY(), -1))): The returns TRUE if a date is before the last day of the previous 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