COUNTIF Examples in Excel
Example of Countif Function is =Countif(A:A,”Anand”) this will give us the count the number of times Anand is present in the cell range A:A, =countif(A1:B7,”Excel”) this will count the number of times excel word appears in the cell range A1 to B7.
Example #1
Now take a look at the simple example of counting numbers from the large list. Take an example of the below data.
From the above list, I want to count how many times 15 number is there in the list.
- Let’s open the COUNTIF function.
- The first criterion is the range. This is our list of numbers, so select the range as A2 to A10.
- The next and final part is to tell the criteria. Criteria are nothing but what we need to count. Mention 15 as the criteria.
Note: if the criteria are text value then we need to mention in double-quotes, if the criteria are numerical value then we need not mention in double-quotes.
- Click Enter and we will get the answer.
So the total count of the number 15 is 2.
Example #2
Now take a look at the example of counting text values. Consider the below data for the example.
- From this list, we need to count the words Government. Let’s apply the COUNTIF function to get the total.
- Since we are counting text value here, we need to supply the criteria in double-quotes. Mention the criteria as “Government”
- Click Enter and we will get the answer.
There are totally 6 time Government word appearing in the range A2 to A11
Example #3
In the above example, we have counted the word Government. What if we need to count the other words from the range? Applying COUNTIF to all the criteria does not make any sense.
Let’s create a drop-down list of all the three values in the range i.e. Government, Private, Midmarket.
- Select a cell where you want to create a drop-down list. Go to the data tab, select data validation.
- After selecting Data validation the following window will open.
- In Allow section choose a List and in the Source type Government, Private, Midmarket.
- Then click ok and you get a drop-down list at a selected cell.
- After creating the drop-down list apply the formula but don’t write the criteria in words rather give a link to the drop-down cell. Refer below image for your reference.
I had given a link to the cell C2 for criteria, the cell C2 holds the drop-down list of all the items. Whenever you change the value in the drop-down cell (C2), COUNTIF gives the count of the selected value.
Example #4
At the beginning itself, I told you that COUNTIF can count only one item at a time. But we can apply some other logic to make it count multiple values as well. Mainly there are two techniques to do this.
Technique 1
We can add two COUNTIF functions one after the other to get the count of two or more values. For example, take a look at the below data.
- I want to count Government as well as Private from the list. Firstly, as usual, apply the COUNTIF function for counting Government words as you have learned in the previous example.
This is easy now because we have learned in the previous example. Here you need to use your brain to the next level.
- Firstly understand the question we are answering, the question is we need the total count of Government & Private. After the first formula add plus (+) symbol and start one more COUNTIF function and count Private word in this COUNTIF function.
- Click Enter and you will get the answer.
Wow!!! We got the value. Yes first COUNTIF returns the count of the word Government and the second COUNTIF returns the count of the word Private. Since we put the plus (+) symbol in between these two COUNTIF functions it will give us the total of numbers count given by these two COUNTIF functions.
Technique 2:
Second technique does not require two COUNTIF functions to pass. We can count these two values in a single COUNTIF itself.
- As usual, you open the COUNTIF function.
- In the criteria, the argument opens a curly bracket and supply two criteria instead of regular one criteria.
- Now here COUNTIF alone cannot give me the result here. Since we are adding up two values here we need to apply SUM function before the COUNTIF function.
- Now hit enter this will give you the total count of Government & Private.
Example #5
We can also use COUNTIF with operator symbols. For example in a list of numbers what if you want to count numbers that are greater than a certain number.
- Consider the below data for an example.
- In order to count the numbers which are greater than 20. Open the COUTNIF function and select the range first.
- In the criteria firstly mention the greater than symbol (>) in double-quotes.
- Now supply the criteria number with ampersand (&) symbol.
- We have got the total count of numbers which are greater than 20.
Example #6
Now we will see how to count values that are not equal to a certain value. Take a look at the below COUNTIF example
- From this list, we need to count the values which are not equal to the word Government. This means we need to count all the values except the Government. Apply below formula to get the total.
- The total count of the words which are not equal to the word Government is 4.
Things to Remember
- All the text value criteria should be supplied in double-quotes.
- Numerical values need not be supplied in double-quotes.
- All the operator symbols also need to supply with double-quotes.
- If the text value is referring to the cell then no need for double-quotes.
Recommended Articles
This has been a guide to COUNTIF Examples in Excel. Here we discuss examples of COUNTIF Function in excel using a Dropdown list, Operator Symbol with downloadable excel template. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
