How to use OR Logic in COUNTIF/COUNIFS in Excel

How to use COUNTIF/COUNTIFS OR

Have a look at below data where you have a list of people and their favorite social media platform.

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.

=SUM(COUNTIFS(B2:B51,{“Twitter”,”Facebook”}))

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 Facebook (17) + Twitter (8) combined.

How it works

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 with these functions you need to split this formula into three parts.

how countif or formula works
  1. First of all, we have used two different criteria in this formula by using array concept.
  2. And after that, we have used COUNTIFS to look for these two values.
  3. In the end, we 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 COUNTIF but the in COUNTIFS formula will work in the same way.

Dynamic OR Condition

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 table) of your values.
  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

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?

Share your views with me in the comment section, I’d love to hear from you. And, please don’t forget to share this tip with your friends.

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

    • Puneet Gogia

      You’re Welcome

  • Daryl Fagala

    Good post! But the article’s title and conclusion, as well as a lot of verbiage in between, are about COUNTIF/COUNTIFS “OR”. I can’t find “OR” in any of the example formulas. Am I missing something?

    • Puneet Gogia

      ha ha ha You gave me a laugh.
      I think I missed something while explaining, I’ll try to improve. But the thing is it’s not about combing COUNTIF with OR but adding a OR effect in it.

      • Daryl Fagala

        Thanks for the reply. I’m looking forward to learning more about COUNTIF…OR. Also, thanks for the many email newsletters that I’ve found extremely useful. I’ve been an Excel developer for 19-plus years and I continue to be amazed by the things you do with formulas. Keep ’em coming!

        • Puneet Gogia

          You’re Welcome.

  • Najib Beg

    I am enjoying weekends by applying your excel tips which I received by email, it’s very useful and apply it in my daily at work and remind functions of excel. I appreciate your work, God bless you always.

    • Puneet Gogia

      Thanks, Najib. Your words are blessings.