COUNTIF Examples in Excel (Step by Step)

Table of Contents

Examples of COUNTIF Function in Excel
COUNTIF Examples in Excel

COUNTIF Examples in Excel

COUNTIF as the name itself tells Count values in the range based on the criteria given by the user.

Don’t get confused with COUNTIF & COUNTIFS, COUTNIF will count the value we supplied based on single condition, whereas COUNTIFS will count the value based on multiple criteria’s. In this article, we will see only the examples which are related to COUNTIF function right from basic to advanced level.

The Formula of COUNTIF Function in Excel

The syntax of the COUNTIF function includes there are only two criteria’s.

Range: This is nothing but our values range.
Criteria: From the above range what is the thing we need to count. This is referred to as Criteria.

COUNTIF Examples in Excel

Following are the COUNTIF examples in Excel.

COUNTIF Excel 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.

First criteria is a range. This is our list of numbers, so select the range as A2 to A10.

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 is text value then we need to mention in double quotes, if the criteria is 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.

COUNTIF Excel 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 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 – Example of COUNTIF with Dropdown List

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’s 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 – Count Multiple Values Using COUNTIF Function

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 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 word 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, 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 – COUNTIF with Operator Symbols

We can also use COUNTIF with operator symbols. For example in a list of numbers what if you want to count numbers which 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.

COUNTIF Excel Example #6

Now we will see how to count values which are not equal to 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 about COUNTIF Excel Examples

All the text value criteria’s 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.

You can download this COUNTIF Examples Excel Template here – COUNTIF Examples Excel Template

COUNTIF Examples