HomeGoogle DocsSpreadsheetCOUNTIF to Count by Month in a Date Range in Google Sheets

COUNTIF to Count by Month in a Date Range in Google Sheets

Published on

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))
COUNTIF to Count by Month in Google Sheets

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.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

40 COMMENTS

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here