Excel Count Months in a Column( 4 Quick Ways)

Using SUMPRODUCT

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.

Sample Dataset

Download to Practice

4 Ways to Excel count months in a column

1. Using SUMPRODUCT 

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

Firstly, select the cell where you want to place your resultant count of months.
➤ I selected the cell E4
Secondly, type the formula in the selected cell or the Formula Bar.
The Formula is

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

Using SUMPRODUCT

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.

Finally, press ENTER.
Now, from the Date column, it will count how many times the same months are occurring.

Using SUMPRODUCT

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

Using SUMPRODUCT

2. Using SUM and IF

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

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

Using SUM and IF

First, select the cell where you want to place your resultant count of months.
➤ 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 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 to count months

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

Using SUM and IF to count months

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.

I’ve taken a Date-Month column to calculate the month of the Date column.
To use the MONTH function first, select the cell where you want to place your resultant value.
➤ I selected the D4 cell.
Then, you can type the formula in the selected cell or the Formula Bar.
The Formula is

=MONTH(C4)

Using MONTH function

Finally, press ENTER.
Then, it will show the months of the date given in the Date column.

Using MONTH function

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

Using MONTH function

Now, I’ve taken a 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

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

Converting Date into month name

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

Count Date Records by Month in a Column 

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.

Using COUNTIF function

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

Using COUNTIF function

4. Using COUNTIFS and EDATE Function

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

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

Using COUNTIFS and EDATE Function

To use this function first, select the cell to place your counted months.
➤ I selected the 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))

Using COUNTIFS and EDATE Function

Finally, press ENTER.
Now, it will count how many times the same months are occurring in the Date column.

Using COUNTIFS and EDATE Function

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

Using COUNTIFS and EDATE Function

Practice

I’ve 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 sheet

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. If you have any kind of suggestions, ideas, and feedback please feel free to comment down below.

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. 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

ExcelDemy
Logo