Here’s a cool tip to learn about the COUNTIF function. You can use the COUNTIF function to count by month in Google Sheets.
Suppose you want to count the number of specific transactions that occurred in a particular month from a date column. How can you do that?
There are several options, but the simplest one is to use the COUNTIF function. See the following example to learn how to count a date column for the occurrences of months.
How to Use COUNTIF to Count by Month in a Date Range
See the sample data and the formula used:
=ARRAYFORMULA(COUNTIF(MONTH(A2:A), 6))
For this purpose, we can use the MONTH function together with COUNTIF. Simply wrap the COUNTIF range with the MONTH function and use the month number as the criterion. Also, don’t forget to use the ARRAYFORMULA as we are applying the MONTH function to an array.
In the example above, I used the number 6 as the criterion, representing the month of June.
The above is a basic example of how to use COUNTIF to count by month in Google Sheets.
See how the formula works!
Steps:
The following MONTH formula returns the month numbers:
=ARRAYFORMULA(MONTH(A2:A7))
Consider these month numbers as the range in COUNTIF. Then use the number 6 (June) as the criterion to return the count of occurrences in June within the range.
=COUNTIF(ARRAYFORMULA(MONTH(A2:A7)), 6)
You can move the ARRAYFORMULA to the beginning without affecting the formula output. Here’s the final formula:
=ARRAYFORMULA(COUNTIF(MONTH(A2:A), 6))
Update:
I couldn’t foresee an error in the above formula. If you use 12 as the month number, the formula might return an incorrect output. The reason is blank cells, which would return 12 as the month number. To address this issue, use the following formula:
=ARRAYFORMULA(COUNTIF(MONTH(DATEVALUE(A2:A)), 6))
Wrapping the DATEVALUE converts the dates to corresponding date values and returns errors in blank cells. This helps avoid issues in COUNTIF when the criterion is 12.
COUNTIF to Count by Month and Year
To count data for a specific month in a date range spanning multiple years, isolate the desired year to prevent counting entries from other years in that month. Here is how to achieve this using the EOMONTH function and DATE function within the COUNTIF formula.
Convert virtually all the dates in the date range to the beginning of the month dates, and instead of using the month number, use the beginning of the month date as the criterion.
For example, to count the value in June 2018, instead of specifying the month number 6, you can specify DATE(2018, 6, 1)
, which is in the format DATE(year, month, day)
.
Here is how to use COUNTIF to count by month and year in a date range in Google Sheets:
=ARRAYFORMULA(COUNTIF(EOMONTH(A2:A, -1)+1, DATE(2018, 6, 1)))
Tips for Those Who Want to Learn COUNTIF by Month in QUERY
The QUERY function incorporates the MONTH and YEAR scalar functions, making it easy to obtain counts by month and year.
You can replace the above COUNTIF formulas with the QUERY function as shown below.
Month:
=QUERY(A2:A, "Select Count(A) where month(A)=5")
Month and Year:
=QUERY(A2:A, "Select Count(A) where month(A)=5 and year(A)=2018")
I know you can easily understand these formulas, but be mindful of one thing. For the count of June, use the number 5, not 6. This is because, in QUERY, the month number ranges from 0 to 11, not from 1 to 12
SUMPRODUCT to Count by Month in Google Sheets
The SUMPRODUCT function can also count by month. This code is clean as there is no need to use ARRAYFORMULA with it.
Month:
=SUMPRODUCT((MONTH(A2:A)=6) * (A2:A<>0))
Month and Year:
=SUMPRODUCT((MONTH(A2:A)=6) * YEAR(A2:A)=2018)
Resources
We have seen the usage of COUNTIF, QUERY, and SUMPRODUCT to count by month in a date range in Google Sheets. Here are a few more Google Sheets tutorials that explore the COUNTIF/COUNTIFS functions.
- How to Perform a Case Sensitive COUNTIF in Google Sheets.
- Countifs with Multiple Criteria in the Same Range in Google Sheets.
- Countif in an Array in Google Sheets Using Vlookup and Query Combo.
- How to Use COUNTIF with UNIQUE in Google Sheets.
- Google Sheets: Countifs with Not Equal to in Infinite Ranges.
- COUNTIFS in a Time Range in Google Sheets [Date and Time Column].
- Not Blank as a Condition in Countifs in Google Sheets.
- Countifs with Isbetween in Google Sheets.
Hi,
I have random dates in column A and random names in column B. I want to count how many times a value or name in column B occurs with respect to the month (column A).
Hi Lenny,
I suggest you try this QUERY formula:
=QUERY(HSTACK(ARRAYFORMULA(EOMONTH(A1:A,-1)+1),B:B),"SELECT Col1,Col2, COUNT(Col1) WHERE Col2 IS NOT NULL GROUP BY Col1,Col2")
You can find more information about this usage here: How to Group Data by Month and Year in Google Sheets.