How to Count Months in a Column in Excel: 4 Quick Methods

Method 1 – Apply SUMPRODUCT and MONTH Functions Combined to Count Months

Steps:

Select the cell where you want to place your resultant count of months.
Cell E4 Is selected.
Type the formula in the selected cell or the Formula Bar.
The Formula is

=SUMPRODUCT(--(MONTH($C$4:$C$10)=MONTH(C4)))

Press ENTER.
From the Date column, it will count how many times the same months are occurring.

N.B: Don’t forget to use the — sign in the SUMRPRODUCT function otherwise it will show 0. This unary operator converts TRUE and FALSE into 1 and 0.

Use the Fill Handle to AutoFill formula for the rest of the cells.

Method 2 – Count Months in a Column Using SUM and IF

Steps:

In the Months column, I’ve taken the months as a number to use the SUM and IF functions.

Select the cell where you want to place your resultant count of months.
We selected the F4 cell.
Type the formula in the selected cell or the Formula Bar.
The Formula is

=SUM(IF(MONTH($C$4:$C$10)=E4,1,0))

Press ENTER to run the formula.
Using the month’s number from the Months column will count how many times the same months are occurring from the Date column.

Use the Fill Handle to AutoFill formula for the rest of the cells.

Method 3 – Count Date Records by Month in a Column

Steps:

We took a Date-Month column to calculate the month of the Date column.
Use the MONTH function first, and select the cell where you want to place your resultant value.
We selected the D4 cell.
Type the formula in the selected cell or the Formula Bar.
The Formula is

=MONTH(C4)

Press ENTER.
It will show the months of the date given in the Date column.

Use the Fill Handle to AutoFill the formula for the rest of the cells.

We took the Months column to keep the respective month’s name of date. To convert the dates into months’ names first select the date.
Open the Home tab >> From the Number group >> select Down Arrow.

It will pop up a dialog box. Select Custom and type mmmm in Type.
Click OK.

Months are converted into only months’ names.
To count the months first select the cell where you want to keep the counted months value.
I selected the F4 cell.
Type the formula in the selected cell or into the Formula Bar.
The Formula is

=COUNTIF(D$4:D$10,MONTH(F4))

Press ENTER to run the formula.
Using the month’s name from the Months column will count how many times the same months are occurring from the Date-Month column.