Countifs with Multiple Criteria in Same Range in Google Sheets

The use of Countifs with multiple criteria in the same range differs in Google Sheets and Excel. In this tutorial, you can learn about that usage.

Actually, in Google Sheets, you can use Query as Countifs alternative which is far better to count and handle criteria. But here I am going to stick with Countifs! Do you know why?

It’s a fact that many of the users who migrated from Excel to Google Sheets are not familiar with using Query. So I dedicate this tutorial to such users.

The purpose of using Countifs is to use multiple criteria in more than one column. But sometimes there may be conditions to be tested in the same column multiple times along with conditions in another column. This tutorial is based on this context.

In the above scenario, in Google Sheets, you can intelligently replace Excel’s Countifs formula with an IF + Countif formula or Countifs itself. I am including both solutions here in this post.

Countifs OR Criteria Within Curly Brackets in Sheets and Excel

To use multiple criteria in the same column range in COUNTIFS, we should make use of the Curly Braces as below. It applies to both Excel and Google Sheets.

{"Apple","Orange","Pomegranates"}

Here is one example formula (Excel) for multiple criteria in the same column range in Countifs.

=SUM(COUNTIFS(A:A,{"APPLE","ORANGE","POMEGRANATES"}))

Note: You can scroll down to see Formula 1 where I’ve used the Curly Braces as OR condition in the same column, in Countifs in Excel. Also, I have included conditions from one another column in that.

There is a big difference in the usage of Curly Braces in Excel and Google Sheets. Of course, in Google Sheets also we can use the Curly Braces to generate Arrays. But not the same way as in Excel.

In Google Sheets, additionally, you must use ArrayFormula with Curly Braces and that makes a lot of difference.

=ArrayFormula(SUM(COUNTIFS(A:A,{"APPLE","ORANGE","POMEGRANATES"})))

Countifs with Multiple Criteria in the Same Range in Excel

I am making use of the below two-column sample data set for our example purpose.

Examples to Multiple Countifs Criteria in Same Range

I just want to count the number of occurrences of different fruit items on a particular date.

Here I am using today’s date as that particular date and as per my system, today’s date is 31/01/2018.

I only want to count the items “Apple”, “Orange” and “Pomegranates” that appear on this date.

First, let’s see the use of the COUNTIFS formula in Excel. Then we can see how to use Countifs with multiple criteria in the same range in Google Sheets.

Formula 1:

=SUM(COUNTIFS(A:A,TODAY(),B:B,{"APPLE","ORANGE","POMEGRANATES"}))

In Excel, it’s quite easy. Excel accepts the OR condition inside curly braces as above. This formula counts all the items named “Apple”, “Orange” and “Pomgranges” in column B if the corresponding date in column A is 31/01/2018.

Countifs with Multiple Criteria in the Same Range in Google Sheets

Now Let’s learn how to use Countifs with multiple criteria in the same range in Google Sheets.

Many Google Sheets users, who are familiar with Excel, try the above formula and jump to the following conclusion. I mean they will advise you like;

In Google Sheets, you can’t use the formula above. Multiple criteria in Countifs in the same column are not possible or recommended in Google Sheets.

It’s not correct. We can include multiple conditions from the same column (OR criteria in the same column) and also from a different column in Countifs.

What you want to do is to wrap the above Excel formula with the Google Sheets ArrayFormula function.

Formula 2:

=ArrayFormula(SUM(COUNTIFS(A:A,TODAY(),B:B,{"APPLE","ORANGE","POMEGRANATES"})))

Countif + If Alternative to Countifs in Google Sheets

In Google Docs Sheets, the above Countifs formula can be intelligently replaced by an IF+ Countif combination.

Formula 3:

=ARRAYFORMULA(SUM(COUNTIF(IF(A1:A=TODAY(),B1:B),{"APPLE","ORANGE","POMEGRANATES"})))

Formula Explanation:

The use of the IF function in this formula is to return all the values in column B if the corresponding values in Column A are today’s date.

I’ve extracted the IF part from the above formula for your reference and applied it in cell D4. See that below.

=ArrayFormula(if(A1:A10=today(),B1:B10))

The use of ArrayFormula and If function in Countif

The formula is returning FALSE wherever the date is not matching. Now I am using a Countif formula keeping the above D4:D13 as the range.

=ArrayFormula(sum(countif(D4:D13,{"Apple","Orange","Pomegranates"})))

This formula counts all the values in the range D4:D13 for items “Apple”, “Orange”, and “Pomegranates”. This’s what I have done in our main formula (Formula 3).

Conclusion

With this tutorial, I hope, I could shed some light on the above and the following similar user queries.

Countif OR – How to use OR in the Countif function in Google Sheets.
Countifs OR – How to use OR in the Countifs function in Google Sheets.
Same field twice or more times in Countifs, how to?
The use of Curly Brackets in Countif and Countifs in Sheets.

That’s all. See you again with another advanced Google Sheets tutorial.

Additional Resources:

37 COMMENTS

Chris Slater May 24, 2023 At 11:44 pm

I hope you can help. I’m trying to count the number of TRUE values (checked boxes) in a given range of cells based on a range of dates defined in two other cells.

And to top it off, if there is no defined “end date,” can I get it to use today as the end date instead?

I was trying with =COUNTIFS(C10:DD10, true, $C$4:$DD$4>=G62,$C$4:$DD$4<=H62,$C$4:$DD$4<=TODAY()) but that returns an error that says COUNTIFS expects all arguments after position 2 to be in pairs.

I tried adding an IF statement in there, but that didn’t work, either.

Can you help? Thanks!

- Prashanth May 25, 2023 At 8:11 am
  
  Hi, Chris Slater,
  
  This may work.
  
  =countifs(C10:10,true,C4:4,">="&G62,C4:4,"<="&if(H62,H62,today()))
  
  - Chris Slater May 25, 2023 At 6:22 pm
    
    Brilliant! It works. Thank you!
    
Raffi June 3, 2022 At 12:16 pm

Is it possible to choose one of such criteria?

If I select Apple in a drop-down, the only value that should appear is the count of Apple.

If I leave the drop-down blank, then the values that should appear are the count of all/specified fruits/values.

I use the following formula:

=ARRAYFORMULA(SUM(COUNTIFS(A:A;{"D1";{"APPLE";"ORANGE";"POMEGRANATES"}};B:B;"1/6/2022")))

*D1 = Dropdown for Apple/Orange/Pineapple/Pomegranates

Here is the demo: – URL removed by the Admin –

- Prashanth June 5, 2022 At 10:19 am
  
  Hi, Raffi,
  
  I understood your points. You want to COUNTIFS with multiple criteria or a single criterion based on the drop-down in cell D1.
  
  This will do that.
  
  =ARRAYFORMULA(SUM(COUNTIFS(A:A,if(len(D1),D1,{"APPLE";"ORANGE";"PINEAPPLE";"POMEGRANATES"}),B:B,"1/6/2022")))
  
  And as per your Locale (spreadsheet settings), use it as,
  
  =ARRAYFORMULA(SUM(COUNTIFS(A:A;if(len(D1);D1;{"APPLE";"ORANGE";"PINEAPPLE";"POMEGRANATES"});B:B;"1/6/2022")))
  
Rashid Qureshi April 28, 2022 At 7:55 am

I want to use this but it’s not working can someone help…

=if(F5="","", COUNTIFS($W5:$BW5,{"Present","Present H"})/COUNTIFS($W$4:$BW$4,"*****"))

- Prashanth April 28, 2022 At 10:35 am
  
  Hi, Rashid Qureshi,
  
  Try this.
  
  =ArrayFormula(IFERROR(if(F5="","", SUM(COUNTIFS($W5:$BW5,{"Present","Present H"}))/ COUNTIFS($W$4:$BW$4,"*****"))))
  
Chris October 5, 2021 At 9:44 pm

=SUM(COUNTIFS(D2:D50,{"Sr.";"Jr."},C2:C50,{"M","M/A"}))

This is my excel formula, but when moved to Google Sheets, it doesn’t work.

I have ready to insert ArrayFormula as noted in the example above.

=ArrayFormula(SUM(COUNTIFS(D2:D50,{"Sr.";"Jr."},C2:C50,{"M","M/A"})))

However, this does not work. Please provide any simple suggestions.

- Prashanth October 6, 2021 At 12:23 pm
  
  Hi, Chris,
  
  In Google Sheets, you can try different combinations. Here is one.
  
  Exact Match:
  =counta(filter(C2:C50,regexmatch(C2:C50,"^M$|^M\/A$")* regexmatch(D2:D50,"^Sr\.$|^Jr\.$")))
  
  Partial Match:
  =counta(filter(C2:C50,regexmatch(C2:C50,"M|M\/A")* regexmatch(D2:D50,"Sr\.|Jr\.")))
  
Omid D Danielpour September 8, 2021 At 12:24 am

I am trying to get a count if for Tuesdays that have a greater value than 90 in cells M2:M145 in google sheets. What am I doing wrong here?

=(COUNTIFS('POSITIVE CLOSE'!A2:A145,"Tue",M2:M145,">90"))

- Prashanth September 8, 2021 At 10:53 am
  
  Hi, Omid D Danielpour,
  
  Your formula seems correct to me.
  
  If you have dates in A2:A145 formatted as day names, try the below formula.
  
  =ArrayFormula(COUNTIFS(text('POSITIVE CLOSE'!A2:A145,"ddd"),"Tue",M2:M145,">90"))
  
  Also, check the locale settings of your Sheet.
  
  - Omid D Danielpour September 13, 2021 At 6:59 pm
    
    Hi Prashanth,
    I am trying to count a series of data in a column, say M2:M145.
    I want it to count all instances where values were positive in a series of 3 times or more.
    Meaning, If M2, M3, M4 were positive in value, it would count as 1.
    If M6, M7, and M8 were positive, count it and add up to 2, etc.
    If M9 and M10 were positive but M11 was negative, it would not add and move on through the column.
    
    - Prashanth September 14, 2021 At 8:55 am
      
      Hi, Omid D Danielpour,
      
      What would be the count if M5 was positive?
      
      - Omid D Danielpour September 16, 2021 At 6:02 pm
        
        Hi, Prashanth,
        
        Thanks for the follow-up.
        
        If M5 in that series is positive as well, the count would remain unchanged, and it would count it as 1 still.
        
        It’s looking to identify three or more uninterrupted occurrences of positive values.
        
        If M6, M7, and M8 were positive, that series would still count as just 1.
      - Prashanth September 18, 2021 At 10:43 am
        
        Hi, Omid D Danielpour,
        
        I have tested the below formula, and it seemed to work.
        
        =ArrayFormula( countif( countif(filter(lookup(row(M1:M145),if({-1;M2:M145}<0,row(M1:M145))),{-1;M2:M145}>=0), unique(filter(lookup(row(M1:M145),if({-1;M2:M145}<0,row(M1:M145))),{-1;M2:M145}>=0))) ,">=3") )
        
        You can try it. If you want a formula explanation as a tutorial, please let me know.
Nikita May 18, 2021 At 1:52 pm

This is a very useful post! I am wondering if there would be a way to use it to simplify my sample spreadsheet.

I am collecting dates, types, and names at different sessions, and I am compiling the number of times per week that each person leading a session. Right now I am using COUNTIFS with conditions on the date, type, and name, but I need to add a new COUNTIFS (with the same date and type conditions) for each person leading. Would there be a simpler way to do this?

Thank you for your support!

- Prashanth May 19, 2021 At 9:15 am
  
  Hi, Nikita,
  
  I could partially achieve that with FLATTEN, and then a Query multiple column Pivot.
  
  Please see the tab “Sheet1 – II” in your shared sheet for the formula.
  
  - Nikita May 19, 2021 At 5:58 pm
    
    Hi Prashanth,
    
    Thank you for your quick reply, really appreciate it! That is quite an interesting way to do it, I have a lot more to learn. Thanks again 🙂
    
sujay March 11, 2021 At 11:14 am

Very useful !!

Pavan November 28, 2020 At 12:29 pm

Hi Prashanth,

Please help me to use the below formula in google sheets. In Excel, I am using;

=SUM(COUNTIFS(D1,A4,D4,{"Booked","WIP","Pending","PFC Pending","PFC In Progress","PFC Completed","QC Correction","Client Correction","QC","QC WIP","QC Pending","1 - QC Done","1 - QC WIP","Send for Approval","Hold","Hold - 1 QC","Hold - 2 QC","Uploaded","QC Passed","Dispatch Ready","Approved","Dispatch WIP","Dispatch Needed"}))

- Prashanth November 28, 2020 At 1:02 pm
  
  Hi, Pavan,
  
  Wrap your above formula with the ArrayFormula function as per the below syntax.
  
  =ArrayFormula(your_above_formula)
  
  But for me, it seems the arguments are not properly inputted in your Countifs.
  
  COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
  
  As per your formula, the cells D1 and D4 are criteria_range1 and criterira_range2. Is that correct?
  
  Sharing a sample/mockup sheet will be helpful to understand the issue.
  
Derek October 1, 2020 At 7:04 am

Is it possible to multiply the COUNTIF statements for a range of data?

=countif('title'!A10:A20,"Help")

But then what I want to do is if “Help” shows up, then be able to multiply it by the value that would be entered into B10:B20.

Hope this makes sense. Thanks

- Prashanth October 1, 2020 At 9:11 am
  
  Hi, Derek.
  
  I guess you want to count “Help” in A10:A20, sum corresponding values in B10:B20, and then multiply both the outputs. If so, there are several ways.
  
  Here are a few of them that I may use in such a scenario in Google Sheets.
  
  Using Countif and Sumif:
  
  =countif(title!A10:A20,"Help")*sumif(title!A10:A20, "Help",title!B10:B20)
  
  Using two Filter formulas, Count, and Sum:
  
  =COUNT(filter(title!B10:B20,title!A10:A20="Help"))* sum(filter(title!B10:B20,title!A10:A20="Help"))
  
  Using Query:
  
  =query(title!A10:B20,"Select count(A)* sum(B) where A ='Help' label count(A)*sum(B)''",0)
  
bahar May 29, 2020 At 11:20 pm

Hi Prashanth,

If I say this correctly,

If cell A1 not empty, and if one or more cells in B1:E1 is not empty, how can I count in F1 result as = 1?

Thank you.

- Prashanth May 30, 2020 At 4:21 pm
  
  Try this formula, please.
  
  =if(ArrayFormula(SUM(len(B1:E1)*len(A1)))>0,1,)
  
  - bahar June 1, 2020 At 8:35 pm
    
    Hi, Prashanth,
    
    Thank you for your response, have a great day.
    
    Thank You.
    
Bindu November 8, 2019 At 5:35 pm

Hi,

I tried using the formula but function not working.

I need the count of the words specified in the formula. kindly help Thanks.

- Prashanth November 9, 2019 At 6:54 am
  
  Hi, Bindu,
  
  I have added the following Query on your Sheet.
  
  =query({$A$1:A},"Select Count(Col1) where lower(Col1) contains '"&lower(C9)&"' label Count(Col1)''")
  
  If you want an explanation of this formula, please let me know.
  
  Best,
  
Mohan August 23, 2019 At 11:50 am

Hi Guys,

Can anyone help me out?

How can I execute the below formula?

=COUNTIFS(Sheet1!$AK:$AK,$A23,Sheet1!$AJ:$AJ,"NO",Sheet1!$S:$S,"To be Scheduled Later - Customer","To be Scheduled Later - Rupeek")

- Prashanth August 23, 2019 At 5:37 pm
  
  Hi, Mohan,
  
  I guess there are multiple conditions to be evaluated in column S. Then the COUNTIFS formula would be something like;
  
  =sum(ArrayFormula(COUNTIFS(Sheet1!$AK1:$AK,$A23,Sheet1!$AJ1:$AJ,"NO", Sheet1!$S1:$S,{"To be Scheduled Later - Customer", "To be Scheduled Later - Rupeek"})))
  
  Best,
  
Ryan August 13, 2019 At 11:27 pm

I don’t know when it was added but there is now a =COUNTUNIQUEIFS() function that does exactly this.

- Prashanth August 14, 2019 At 9:08 am
  
  Hi, Ryan,
  
  Thanks a ton for this info!
  
  I think it’s a newly added function in Sheets.
  
Ramana July 18, 2019 At 4:48 pm

Hi Prashanth, I am trying to have more than 2 Countif arguments. Below is the formula

=COUNTIFS('Master Attrition 2018'!B:B,'World Wide - 2018'!$C$5,=Countif('Master Attrition 2018'!P:P,'Master Attrition 2018'!"*Remort*")'Master Attrition 2018'!H:H,'World Wide - 2018'!B7). – Its not working

The reason I wanted to use the 3rd argument because I want to have the part of the data from each of those cells (Remort – from Remort -NJ, Remort-SFO, Remort-NY, Remort-WC). Thanks

- Prashanth July 18, 2019 At 8:52 pm
  
  Hi, Ramana,
  
  Can you prepare a demo Sheet and share here?
  
Erolle August 15, 2018 At 11:04 am

Hi, I’m trying to get the sum of this to Countifs.

=countifs('Masterfile PV Validation'!G:G,A64,'Masterfile PV Validation'!P:P,"Yes") =countifs('Masterfile PV Validation'!G:G,A64,'Masterfile PV Validation'!S:S,"Yes")

- Prashanth August 15, 2018 At 1:49 pm
  
  Hi, Erolle,
  
  You can try this.
  
  =countifs('Masterfile PV Validation'!G:G,A64,'Masterfile PV Validation'!P:P,"Yes")+ countifs('Masterfile PV Validation'!G:G,A64,'Masterfile PV Validation'!S:S,"Yes")
  
  Here is the Sumproduct alternative.
  
  =sumproduct('Masterfile PV Validation'!G1:G=A64,'Masterfile PV Validation'!P1:P="Yes")+sumproduct('Masterfile PV Validation'!G1:G=A64,'Masterfile PV Validation'!S1:S="Yes")
  
  In a second thought, I find you may want to count column G if the values in both column P and S is Yes. If so, give this formula a try.
  
  =sumproduct('Masterfile PV Validation'!G1:G=A64,'Masterfile PV Validation'!P1:P="Yes",'Masterfile PV Validation'!S1:S="Yes")
  
  Hope this helps.
  
Dhaval August 2, 2018 At 10:04 am

Hi Prashanth,

Thanks for the well explained post…really helpful