A few days back I wrote about how we can use SUMIF OR. And today, I want to show that by using the same logic we can create a COUNT OR formula.
Even we can create dynamic criteria in this formula. But first of all, let me ask you something.
Have you ever faced a situation where you need to count more than one value from a single column or a range of cells?
If yes, then using this OR logic in COUNTIF/COUNTIFS can be a game-changer for you.
The thing is, normally, when we COUNTIF we can just count cells by specifying a single condition and with COUNIFS we can specify more than one condition but it uses AND logic to count values.
That means we can’t use these functions to count more than a value from a single column or a range.
But today in this post, I’d like to share with you a simple but powerful way to apply OR condition in COUNIF.
Make sure to download this data file from here and now, let’s get started…
How to use COUNTIF/COUNTIFS OR
Have a look at the below data where you have a list of people and their favorite social media platforms.
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 the below formula in a cell and hit enter.
=SUM(COUNTIF(B2:B51,{"Facebook","Twitter"}))
And, if you want to use COUNIFS then insert the below formula in a cell and hit enter.
=SUM(COUNTIFS(B2:B51,{"Twitter","Facebook"}))
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.
- First of all, we have used two different criteria in this formula by using the array concept.
- And after that, we used COUNTIFS to look for these two values.
- In the end, we have enclosed the entire formula in the SUM function to get the joint count of both of the values.
Here I have explained this concept in COUNTIF but the COUNTIFS formula will work in the same way.
Dynamic OR Condition
When I explained about using OR in SUMIF/SUMIFS, Shay asked me how can he use dynamic values instead of using hardcore values in this formula.
And for this, you need to do just two little amendments to your formula.
- The first is, instead of using curly brackets you need to use a named range (the best way is to use a table) of your values.
- 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))}
Get the Excel File
Conclusion
Using OR logic in COUNIF/COUNIFS can increase its power and save you from 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 these formula tips useful and that they will help you to become more efficient in your work.
So, what do 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.
Great job breaking down the use of OR Logic in COUNTIFS and COUNTIF formulas! I’ve been struggling to use these functions effectively, but your post has really helped clarify things for me.
Hi Puneet, Dynamic count doesn’t work for me. I am using office 365. Believe we need curly brackets at the front and at the end? It just print {=SUM(COUNTIFS(B2:B51,Named_Range))} as it is. Without curly brackets it just give zero.
Hi
I had the same issue initially but what you have to do is type the formula =SUM(COUNTIFS(B2:B51,Named_Range)) then instead of just hitting enter at the end rather hit ctrl + shift +enter … this will populate your cell with the answer and auto enclose your formula with {}
I’m confused…is the formula COUNTIF or COUNIF? My version of Excel (Excel 2016) does not recognize COUNIF.
=SUMPRODUCT(–(B:B=”Facebook “) +(B:B=”Twitter ” ))
This above formula can be used for above questions..
No words to says to be frank I don’t even know we can use countif with sum formula. Amazing Puneet. I hope you will grab more like these amazing feature into our purse
How would you do this inVBA?
I don’t know how but you do guess what we have in mind to look for the solutions which we require most. Plus your tips guides me to explore new avenues regarding Excel problem solving methods. Please do keep up with the good work.
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.
Thanks, Najib. Your words are blessings.
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?
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.
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!
You’re Welcome.
Thank you for the sample that you have noticed.
You’re Welcome
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.
Thank you for these kind words
punnet sir,
pls try to post offset function of excel.
Sure.