How to Use 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.

40 COMMENTS

Lenny September 9, 2023 At 7:58 am

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

- Prashanth September 10, 2023 At 10:19 am
  
  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.
  
David March 30, 2022 At 3:53 am

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?

- Prashanth March 30, 2022 At 12:57 pm
  
  Hi, David,
  
  This may help.
  
  =ArrayFormula(countifs(month(Sheet1!H:H),12,not(Sheet1!H:H=""), true,Sheet1!A:A,Sheet2!A2))
  
  Replace month_number with the required value.
  
  - David April 1, 2022 At 1:23 am
    
    It looks like it’s working, but how would I also add the year and the month?
    
    - Prashanth April 1, 2022 At 8:12 am
      
      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) )
      
      - David April 1, 2022 At 10:04 pm
        
        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.
      - Prashanth April 3, 2022 At 7:25 am
        
        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.
Michael August 17, 2021 At 7:13 am

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.

- Prashanth August 18, 2021 At 4:55 am
  
  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)
  
Elaine May 26, 2021 At 2:17 pm

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.

- Prashanth May 28, 2021 At 11:26 am
  
  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.
  
Naimur Rahman January 4, 2021 At 1:06 pm

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?

- Prashanth January 8, 2021 At 2:17 pm
  
  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.
  
Esa Birx November 6, 2020 At 9:43 pm

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

- Prashanth November 8, 2020 At 2:34 pm
  
  Hi, Esa Birx,
  
  This tutorial may help.
  
  Count Unique Dates in a Date Range – 5 Formula Options in Google Sheets.
  
Kristina Collazo October 9, 2020 At 1:08 am

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

- Prashanth October 9, 2020 At 9:16 am
  
  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.
  
Kasia September 15, 2020 At 3:42 pm

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

- Prashanth September 16, 2020 At 2:33 pm
  
  Hi, Kasia,
  
  Here you go!
  
  =ArrayFormula(countifs(month(Sheet1!H2:H),9, year(Sheet1!H2:H),2020,Sheet1!G2:G,"*online*"))
  
  - Risa February 11, 2021 At 10:43 am
    
    I’m trying to count something similar but this doesn’t work for me.
    
    - Prashanth February 11, 2021 At 1:01 pm
      
      Hi, Risa,
      
      You can reach out to me with an example sheet.
      
Amaresh June 9, 2020 At 4:01 pm

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.

- Prashanth June 10, 2020 At 8:29 am
  
  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,
  
  - Amaresh June 14, 2020 At 11:48 am
    
    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
    
    - Prashanth June 14, 2020 At 12:12 pm
      
      Thanks for your feedback!
      
Harsha April 19, 2020 At 1:59 am

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

- Prashanth April 19, 2020 At 9:32 am
  
  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")
  
Diya February 12, 2020 At 9:20 am

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?

- Prashanth February 12, 2020 At 3:30 pm
  
  Hi, Diya,
  
  You should use column identifiers in capital letters in Query. The formula must be;
  
  =Query(A1:C10,"select A, A+30",1)
  
  Also, do formant the range A2:A10 to number and format the corresponding Query output column to Date. I have already explained the steps below.
  
  How to Add or Subtract N Days to The Dates in a Column in Sheets Query.
  
Kate February 11, 2020 At 4:45 am

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!

- Prashanth February 11, 2020 At 2:04 pm
  
  Hi, Kate,
  
  I have already a post on that. In certain cases, a comma might be replaced by a backslash also.
  
  How to Change a Non-Regional Google Sheets Formula.
  
Rahiza October 31, 2019 At 8:31 am

Hi, if my data is another tab of the same spreadsheet, how do I put it in the formula?

- Prashanth October 31, 2019 At 8:51 am
  
  Hi, Rahiza,
  
  You can include Sheet name like;
  
  'your sheetname'!A2:A
  
  Best,
  
Abubakar June 26, 2019 At 12:03 am

How do you factor on the basis of weeks?

- Prashanth June 26, 2019 At 9:20 am
  
  Hi, Abubakar,
  
  Use the WEEKNUM function instead of the MONTH function.
  
  Best,
  
Darren Coles June 25, 2019 At 4:00 pm

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?

- Prashanth June 26, 2019 At 9:00 am
  
  Hi, Darren Coles,
  
  Thanks for pointing out the error!
  
  You can use the COUNTIFS in that case.
  
  Just change the month 6 to 12 in the formula.
  
  I will update the post too.
  
rob March 22, 2019 At 12:50 pm

How do you factor in Count where years are involved?

- Prashanth March 22, 2019 At 1:39 pm
  
  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.