Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

COUNTIFS and COUNTIF With OR Logic

Two days back I wrote about how you can use OR logic in SUMIFS and SUMIF. And today, I want to show, that you can also use the same OR logic and multiple conditions in COUNIFS and COUNIF.

Even you can create dynamic criteria for OR logic in COUNIFS and COUNIF.

how to use or logic with countif and countifs

Have you ever faced a situation where you need to count more than one value from a single column or a range?

If yes, then using a COUNIFS or a COUNIF with an OR logic is the best solution to this problem.

Normally, if you go with COUNTIF you can just count cells by specifying a single condition and if you go with COUNIFS you can specify more than one condition but it uses AND logic to count values.

That means you can’t use these functions to count more than a value from a single column or a range.

So today, in this post you will learn a simple but power way to applying OR condition in COUNIFS and COUNIF.

Example: Apply OR logic in COUNIFS/COUNIF

Have a look at below data where you have a list of people and their favorite social media platform. Download this sample file from here to follow along.

data to use or logic in countif and countifs

Now, from this list of 50 people, you need to count how many people like “Facebook” and “Twitter”.

  • If you want to use COUNIF then insert below formula in a cell and hit enter.
    • =SUM(COUNTIF(B2:B51,{“Facebook”,”Twitter”}))

countif formula to use or logic in countif and countifs

  • And, if you want to use COUNIFS then insert below formula in a cell and hit enter.

countifs formula to use or logic in countif and countifs

With both of the above formulas, you will get 25 in the result which is the count of both of the values (17+8).

How this OR logic Works in COUNIFS/COUNIF

As I mentioned earlier, COUNTIFS uses AND logic and on the other hand, in COUNIF you can only specify one value to count.

To understand the application of OR you need to split this formula into three parts.

formula to use or logic in countif and countifs how it works

  1. First of all, you have used two different criteria in this formula by using array concept.
  2. And after that, you have used COUNTIFS to look for these two values.
  3. In the end, you have enclosed the entire formula in SUM function to get the joint count of both of the values.

Here I have explained this concept in COUNTIFS but the in COUNTIF formula will work in the same way.

Dynamic OR Condition

Two days back, when I explained about using OR in SUMIF/SUMIFS, Shay asked me that how can he use dynamic values instead of using hardcore values in this formula.

use or logic in countif and countifs shay comment

And for this, you need to do just two little amendments in your formula.

  1. The first is, instead of using curly brackets you need to use a named range (best way is to use an excel table) of your value.
  2. And after that, you need to enter this formula by using Ctrl + Shift + Enter as a proper array formula.

So, now your formula will be:

{=SUM(COUNTIFS(B2:B51,Named_Range))}

dynamic range to use or logic in countif and countifs

Sample File

download sample file to learn more about this tips

Conclusion

Using an OR logic in COUNIF/COUNIFS can increase its power and save you for writing long formulas. And, using dynamic criteria is super awesome. You can change values anytime you want, without changing the formula.

I hope you found this formula tips useful and it will help to become more efficient in your work.

So, what you think about this one. Have you tried this anytime before this? Or have any other method to use instead.

Tell me in the comment section, I would love to hear from you.

Related Function

Related Tip


  • harsh shahi

    Hello Ponit Sir, the way you explain Excel, it is a very good way. This makes us understand easily. I am very grateful to you that you know the excellence of Excel in such a nice and easy way.

    • Puneet Gogia

      Thank you for these kind words

      • harsh shahi

        punnet sir,
        pls try to post offset function of excel.

        • Puneet Gogia

          Sure.

  • mano Amiri

    Thank you for the sample that you have noticed.