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

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 Date. Where 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.

Count Months in a Column in Excel: 4 Quick Ways

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.

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.

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.

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.

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))

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.

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

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.

After that, use the Fill Handle to AutoFill the formula for the rest of the 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.

It will pop up a dialog box. From there select Custom and type mmmm in Type.
Finally, 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.
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.

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

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.

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.

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

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.

Download Practice Workbook

Count Months in a Column.xlsx

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.

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

Count Months in a Column in Excel: 4 Quick Ways

1. Apply SUMPRODUCT and MONTH Functions Combined to Count Months

2. Count Months in a Column Using SUM and IF

3. Count Date Records by Month in a Column

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

Practice

Conclusion

Further Readings

Leave a reply Cancel reply