How to Count Months in a Column in Excel (4 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, you may need to count the months in a column to see in the same month which projects or works are running or had been happened. In this article, I’m going to explain Excel count months in a column.

To make the explanation visible I’m going to use a dataset of project information with the date. There are 2 columns which are Project Name and DateWhere we determined the count of the month in between the dates in the range of cells F4:F10, where we used the COUNTIFS and EDATE Functions.

overview of count months in a column Using COUNTIFS and EDATE Functions


Download Practice Workbook


4 Quick Ways to Count Months in a Column in Excel

Here, we are going to use the below dataset in order to determine the month count in between dates. For avoiding any compatibility issues, try to use the Excel 2365 edition.

Sample dataset for showing how to determine month count between dates in excel


1. Apply SUMPRODUCT and MONTH Functions Combined to Count Months

To count the months in a column you can use the SUMRPRODUCT function.

Steps:

  • Firstly, select the cell where you want to place your resultant count of months.
  • Then, cell E4 Is selected.
  • Secondly, type the formula in the selected cell or the Formula Bar.
  • The Formula is
=SUMPRODUCT(--(MONTH($C$4:$C$10)=MONTH(C4)))
  • Finally, press ENTER.
  • Now, from the Date column, it will count how many times the same months are occurring.

Use of SUMPRODUCT and MONTH functions to count months in a column in Excel

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 respectively.
  • Later, you can use the Fill Handle to AutoFill formula for the rest of the cells.

Utilizing the Autofill feature to fill up the range of cell E4:E8 with month count


2. Count Months in a Column Using SUM and IF

You can count the months using the SUM function within the IF function.

Steps:

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

Template for demonstrating how to count month in between dates in columns

  • First, select the cell where you want to place your resultant count of months.
  • Then, I selected the F4 cell.
  • Second, you can type the formula in the selected cell or the Formula Bar.
  • The Formula is
=SUM(IF(MONTH($C$4:$C$10)=E4,1,0))

Using SUM and IF Function to count months

  • At last, press ENTER to run the formula.
  • After that, using the month’s number from the Months column will count how many times the same months are occurring from the Date column.

Using SUM and IF Function to count months in cell F4

  • Finally, you can use the Fill Handle to AutoFill formula for the rest of the cells.

Fill handle dragged to cell F7 to fill the range of cell F4:F7.


3. Count Date Records by Month in a Column 

You can count date records by month in a column using the MONTH function and the COUNTIF function.

Steps:

  • First, I’ve taken a Date-Month column to calculate the month of the Date column.
  • Then, to use the MONTH function first, select the cell where you want to place your resultant value.
  • Afterward, I selected the D4 cell.
  • Next, you can type the formula in the selected cell or the Formula Bar.
  • The Formula is
=MONTH(C4)
  • Finally, press ENTER.
  • Then, it will show the months of the date given in the Date column.

Months in between Date Records in a Column in Excel using MONTH Function

  • After that, use the Fill Handle to AutoFill the formula for the rest of the cells.

Dragging the Fill Handle to the cell D10 to get the month count for other cells.

  • Now, I’ve taken the Months column to keep the respective month’s name of date. To convert the dates into months’ names first select the date.
  • Then, open the Home tab >> From the Number group >> select Down Arrow.

Converting Date into month name in cell F4 cell

  • It will pop up a dialog box. From there select Custom and type mmmm in Type.
  • Finally, click OK.

Formatting the cell in the Format cell dialog box

  • 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.
  • Now, you can type the formula in the selected cell or into the Formula Bar.
  • The Formula is
=COUNTIF(D$4:D$10,MONTH(F4))
  • Finally, press ENTER to run the formula.
  • Now, using the month’s name from the Months column will count how many times the same months are occurring from the Date-Month column.

Conunt the month amount in between dates using the COUNTIF AND MONTH Function in cell G4

  • Last but not least, you can use the Fill Handle to AutoFill formula for the rest of the cells.

We got the month count in between dates by using the autofill feature.

Read more: How to Count Date Occurrences in Excel


4. Combine COUNTIFS and EDATE Functions to Count Months by Criteria

By using the COUNTIFS function and the EDATE function you can count the months in a column.

Steps:

  • Firstly, 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.

Template to count month in between dates using the COUNTIFS and EDATE Functions

  • Then, to use this function first, select the cell to place your counted months.
  • After that, I selected cell F4.
  • Next, 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))
  • Finally, press ENTER.
  • Now, it will count how many times the same months are occurring in the Date column.

Using COUNTIFS and EDATE Function to count months in a column in Excel

  • From this point, you can use the Fill Handle to AutoFill formula for the rest of the cells.

drag the Fill Handle to cell F10 to get the month count in between dates


Practice

  • Additionally, we have given a practice sheet in the workbook to practice these explained ways to cunt months in a column. You can download it from the above.

Practice section of the workbook where you can implement your learning about counting months in a column in Excel


Conclusion

In this article, I tried to explain 4 easy and quick ways to Excel count months in a column. These different ways will help you to count months from a date. Moroever, ff you have any kind of suggestions, ideas, and feedback please feel free to comment down below.


Further Readings

Shamima Sultana

Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo