# 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.

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

### Method 4 – Combine COUNTIFS and EDATE Functions to Count Months by Criteria

Steps:

• In the Date column, I’ve taken the dates in Short Date format also placed the Date columns values in column E for calculation purposes.

• Use this function first, select the cell to place your counted months.
• We selected cell F4.
• Type the formula in the selected cell or into the Formula Bar.
• The Formula is:
`=COUNTIFS(\$C\$4:\$C\$10, ">="&E4,\$C\$4:\$C\$10,"<"&EDATE(E4,1))`
• Press ENTER.
• It will count how many times the same months occur in the Date column.

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

<< Go Back to Excel COUNT Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF