How to Use COUNTIF Formula in Excel?

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.

how-to-use-COUNTIF-formula

 

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”)

#1-Finding-the-Count-of-an-Exact-Value-in-Excel

 

Note: You can also use cell referencing instead of typing the criteria manually. For example- instead of typing “Male”, you can refer to a cell that contains the word “Male” in the range you selected.

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″)

Finding-the-Count-of-a-Value

 

Note: Don’t forget to type the criteria in quotation marks (““)

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.

Count-Using-Multiple-Conditions

 

Note: It is a good practice to maintain the serial of the condition you specify. So, here I have specified that males earning more than 50000, in the condition I have also set the criteria for males first and then the salary.

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.

Leave a Comment