Table of Contents
To count by month in Excel, simply use the COUNTIFS function to count the number of entries in a specified range of cells that meet the criteria of a given month. For example, to count the number of entries in cells A2:A20 that are for the month of January, use the formula =COUNTIFS(A2:A20,”January”). This will return the number of entries in the specified range that have the criteria of January.
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.