You can use the following formula to count the number of occurrences by month in Google Sheets:
=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 Google Sheets
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:A13)))
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 3 times.
- Month 2 (February) occurred 1 time.
- Month 3 (March) occurred 3 times.
- Month 4 (April) occurred 1 time.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
How to Use COUNTIF Contains in Google Sheets
How to Count Duplicates in Google Sheets
How to Count Specific Words in Google Sheets
