How to Count by Month in Excel


You can use the following formula to count the number of occurrences by month in an Excel spreadsheet:

=SUMPRODUCT(1*(MONTH(A1:A10)=11))

This particular formula counts the number of dates in the range A1:A10 that occur in the eleventh month (November) of the year.

The following example shows how to use this formula in practice.

Example: Count by Month in Excel

Suppose we have the following dataset that shows the sales of some product on various dates:

Now suppose we’d like to count the number of dates by month.

To generate a list of unique month numbers, we can use the following formula:

=SORT(UNIQUE(MONTH(A2:A15)))

We’ll type this formula into cell D2:

Next, we can use the following formula to count the number of dates by month:

=SUMPRODUCT(1*(MONTH($A$2:$A$15)=D2))

We’ll type this formula into cell E2, then copy and paste it into each remaining cell in column E:

From the output we can see:

  • Month 1 (January) occurred 4 times.
  • Month 2 (February) occurred 3 times.
  • Month 3 (March) occurred 1 time.

And so on.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Use COUNTIF with OR in Excel
How to Count If Cells Contain Text in Excel
How to Count Unique Values Based on Multiple Criteria in Excel

Leave a Reply

Your email address will not be published.