How to Count Cells in the Next Month

David

2 years ago

You can use the COUNTIFS or SUMPRODUCT functions to count the number of cells in the next 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 appointments are in the next month?

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

Explanation:

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

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

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

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

Explanation:

Step 1: TODAY(): To have Today's date;
Step 2: EOMONTH(TODAY(),0): The result returns the last day of the current month;
Step 3: (EOMONTH(TODAY(),0)+1): The result returns the first day of the next month;
Step 4: (EOMONTH(TODAY(),1)): The result returns on the last day of the next month;
Step 5: (C2:C12 >= (EOMONTH(TODAY(),0)+1)): The result returns TRUE if the date is after the first day of the next month;
Step 6: (C2:C12 <= (EOMONTH(TODAY(),1))): The result returns TRUE if the date is before the last day of the next 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.