In a previous blog, we showed how to count occurrences in excel by using the COUNT formula.
Today we are going to learn some more advanced counting techniques.
While using the COUNT formula we basically showed how to count values without conditions. But when you add conditions it gets more difficult to find the exact values among thousands of data points.
But Excel has a formula, COUNTIF, that can help us with that.
So, today I will show you how to use the COUNTIF formula in excel.
For example, in the COUNT formula blog, I showed how to count numbers, texts, or blank cells using COUNT, COUNTA, and COUNTBLANK formulas. The formulas showed you how many data points were in the data set.
But it didn’t show which data points were there. For example, it didn’t show how many males or females were there, how many people earned more or less than a certain amount, etc.
With the COUNTIF formula, you can tell excel to use these conditions and count certain data points.
Like the IF formula in excel, you can also use different mathematical operators to define conditions for the COUNTIF formula.
So, let’s look at the different techniques of how to use the COUNTIF formula in excel.
How to Use COUNTIF Formula in Excel?
#1 Finding the Count of an Exact Value in Excel
In our previous example, let’s find out how many males are there in the dataset.
Type the following formula to count males in the data set:
=COUNTIF(B2:B33,”Male”)
If you press enter, you will see that there are 18 males in the data set.
#2 Finding the Count of a Value that is More or Less than a Certain Value
If you want to know, how many people earn more than 50000, you can do that by typing the following formula:
=COUNTIF(E2:E33,”>50000″)
If you press enter, you will see that 15 people earn more than 50000 in the dataset.
#3 Count Using Multiple Conditions
Until now, I have shown you how to use the COUNTIF formula by using only one criterion. There is another formula, namely COUNTIFS which lets you use multiple criteria.
So, now you can combine examples 1 and two and count how many males earn more than 50000. So, type the following formula:
=COUNTIFS(B2:B33,”Male”,E2:E33,”>50000″)
If you press enter, you will find that 8 males earn more than 50000.
Both COUNTIF and COUNITIFS are case insensitive, which means that it doesn’t matter if you type “Males” or “males”, it will show the same result.
Conclusion
There are many other ways you can use the formulas shown in this blog. This blog was just to get you introduced to the formula so that you can practice yourself and find new ways to use the COUNTIF formula in excel.
So, there you go, practice and master how to use the COUNTIF formula in excel.
Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.