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

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

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Compare Two Tables for Differences in Excel

To compare two tables with similar data for differences, you can use the XLOOKUP...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

More like this

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

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

  2. How would I use this function to only count values in the range ‘Sheet 1’!H:H if ‘Sheet 1’!A:A value matches ‘Sheet 2’!A2?

        • Hi, David,

          You can add the year component as below.

          =ArrayFormula(
          countifs(month(Sheet1!H:H),12,
          YEAR(Sheet1!H:H),2023,not(Sheet1!H:H=""),
          true,Sheet1!A:A,Sheet2!A2)
          )

          • Hi Prashanth, this is amazing! Could I use the SUMIFS or another function to add values based on these same conditions? The cells to be added together are on Sheet1!F:F, not on column H this time.

            • Hi, David,

              This will do.

              =ArrayFormula(sumifs(Sheet1!G:G,Sheet1!A:A,A2,
              month(Sheet1!H:H),12,year(Sheet1!H:H),2022,
              not(Sheet1!H:H=""),TRUE))

              When you use the year component, that last criteria range and criterion, i.e., not(Sheet1!H:H=""),TRUE, are optional.

  3. Hi Prashanth

    Love your work. It has helped me out so much.

    Is it possible to uses this formula then search by name?

    I.e., John Smith 1 entry in August, so comes back with one (the name is a separate column to date)

    Thank you for your assistance.

    • Hi, Michael,

      Here are the formulas.

      Using Countifs()
      =ArrayFormula(countifs(month(A2:A),8,len(A2:A),">0",B2:B,"John Smith"))

      Using Sumproduct()
      =sumproduct(month(A2:A)=8*(B2:B="John Smith"),len(A2:A)>0)

  4. This is really helpful thank you for this.

    Hoping for some help with the following

    I have a spreadsheet with dates in one column and in another column I have figures. I copied the formula from here to count how many entries in months, which has worked a treat.

    My next step is to count how many entries in that month and add up the figures in the other column that correspond to the entries for that month so I get a running total of how much cash was accounted for in that month. Hope that makes sense

    Many thanks for any assistance.

    • Hi, Elaine,

      Try the below SUMIFS.

      =ArrayFormula(sumifs('Ongoing Cases'!E6:E,month('Ongoing Cases'!D6:D),12,not(len('Ongoing Cases'!D6:D)),FALSE))

      If this formula is not working, you may consider sharing a sample sheet in your next comment below.

  5. Here are a total of 5 dates.

    2020-11-18
    2020-11-01
    2020-11-18
    2020-10-15
    2020-12-20

    Oct = 01, Nov = 03, Dec = 01. I want to count day by month. How is it possible?

    • Hi, Naimur Rahman,

      Assume the above dates are in A2:A.

      In cell B2, insert the following Query.

      =query(A2:A,"Select month(A)+1,count(A) where A is not null group by month(A) label month(A)+1'Month'")

      It will return the output as a table. So you must empty B2:C before inserting the Query in B2.

  6. Hi Prashanth,

    Thanks for the work you have done.

    I am trying to countunique on Google sheets. Basically, I have got a date column where dates against purchase amounts are entered. Sometimes, 3 to 4 purchases are entered on the same dates.

    I wish to countunique dates for the month, eg. each month will have either 30 days, 31 days, etc. when the count formula value is returned for that particular date range.

    In the below example, I am trying to count unique dates between 1/11/2020 and 1/12/2020

    =arrayformula(COUNTIFS(DAYS(Date),)Date,">=1/11/2020",Date,"<1/12/2020"))

    Please could you kindly help?

    Many thanks

  7. I would like the formula to not return a zero but to leave the cell blank instead if they are no September responses. How would that look like? Here is my formula: =ARRAYFORMULA(COUNTIF(MONTH('Form Responses 1'!A:A),9))

    • Hi, Kristina Collazo,

      I prefer to use a Filter as below.

      =if(ifna(filter(A1:A,month(A1:A)=9))>0,
      counta(ifna(filter(A1:A,month(A1:A)=9))),)

      The Countif has one issue, that it counts month(blank) as 12 (December). I have already mentioned the same within the post.

  8. Hi! How would you add another range to this?

    So if I wanted to search for the date and also if it includes a certain word in another column?

    This is what I have;

    =ArrayFormula(countifs(month('Sheet1'!H2:H),9,year('Sheet1'!H2:H),2020)

    But I need it to count month September AND if column G includes the words ‘Online’.

  9. Hi Prashanth,

    I wanted a summary formula to fetch count of different dates placed in one single column “D”
    For example, I could figure out

    Today: COUNTIF(D2:D,TODAY()) which is giving me a count of records with current dates
    Tomorrow: COUNTIF(D2:D,TODAY()+1) which is giving me a count of records with tomorrow dates
    Yesterday: COUNTIF(D2:D,TODAY()-1) which is giving me a count of records with yesterday dates

    Similarly, Can you please help me to fetch the count of following

    This Week:
    This month:
    This Year:

    Data: Column D
    6/8/2020
    6/8/2020
    6/9/2020
    6/11/2020
    6/26/2020
    7/9/2020
    7/9/2020
    1/21/2021
    2/25/2021

    Expectation:

    1. Since I am in the current week of 8th June 2020 to 14th June, I am expecting “This week” count as 4 including weekends.
    2. Since I am in the current month of June, I am expecting “This month” count as 5.
    3. Similarly, “This Year” count as 7.

    • Hi, Amaresh,

      Here are the required formulas.

      This week:

      =ArrayFormula(COUNTIF(weeknum(D2:D),WEEKNUM(TODAY())))

      This Month:

      =ArrayFormula(COUNTIF(month(D2:D),month(TODAY())))

      This Year:

      =ArrayFormula(COUNTIF(year(D2:D),YEAR(TODAY())))

      Best,

      • Thanks a ton, Prashanth. This worked for me. I just started learning and trying to understand the formulas. Enjoying those. You have very nice articles and topics to learn more. I would try to cover each of your articles one by one as per my requirement and keep bugging you more.

        Regards
        Amaresh

  10. Hi,

    I have random dates in col A and Random Names in col B so I wanted to count how many times a value or name in col B is occurring with respect to dates(Col A).

    • Hi, Harsha,

      To include conditions in the count, the suitable function is COUNTIFS.

      =countifs(A2:A7,date(2020,4,19),B2:B7,"Harsha")

      The above formula counts how many times the name “Harsha” appears against the date 19/04/2020.

      This will create a group-wise count summary of the names in column B.

      =query(A1:B,"Select A,B, count(B) where A is not null group by B,A")

  11. Hi,

    I need to add 30 days to a given date. I need to incorporate this in a spreadsheet query.

    How can we do it?

    I tried;

    =Query(a1:c10,"select a, a+30",1)

    where A is the date column. But this is throwing Error. Could you help me with this?

  12. I kept getting Formula Parse Error only to realize that I have to use a semicolon instead of a comma because there are different syntaxes in different countries.

    But thanks for clarifying all this!

  13. Hi Rob,

    These formulas work great for me except for December. When I put in a number 12 it seems to give me an empty cell count function that reduces as non-December dates are entered rather than a count of December dates.

    Have you come across the issue before or understand how to rectify it?

    • Hi, Darren Coles,

      Thanks for pointing out the error!

      You can use the COUNTIFS in that case.

      Countif december month excluding blank cells

      Just change the month 6 to 12 in the formula.

      I will update the post too.

    • Hi, Rob,

      You may want to use Countifs in that case.

      =ArrayFormula(countifs(month(A2:A),6,year(A2:A),2018))

      Interestingly, you can use/tweak the Countif too.

      =ArrayFormula(countif(month(A2:A)&year(A2:A),"72018"))

      In this, the string “72018” represents the 7th month in 2018.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.