COUNTIF Excel Examples

COUNTIF Examples in Excel

COUNTIF in Excel is an in-built statistical function that counts those cells (having date, number, or text) which fulfil a specific criterion or condition. For example, COUNTIF (A:A, “Mark”) will count the number of times “Mark” is displayed in a cell range.

Let us discuss some of the basic and advanced examples of COUNTIF Excel Function

Example #1

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

Now take a look at the simple example of counting numbers from the large list. Take an example of the below data.

data to count numbers

From the above list, I want to count how many times 15 number is there in the list.

  • Let’s open the COUNTIF function.
countif function
  • The first criterion is the range. This is our list of numbers, so select the range as A2 to A10.
excel countif example 1.3
  • The next and final part is to tell the criteria. Criteria are nothing but what we need to count. Mention 15 as the criteria.
excel countif example 1.4

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.
excel countif example 1.5

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.

data to count text values
  • From this list, we need to count the words Government. Let’s apply the COUNTIF function to get the total.
countif function
  • Since we are counting text value here, we need to supply the criteria in double-quotes. Mention the criteria as “Government.”
excel countif example 2.3
  • Click Enter, and we will get the answer.
excel countif example 2.4

There is a totally six-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.

how to select data validation
  • After selecting Data validation, the following window will open.
data validation window
  • In the Allow section, choose a List and in the Source type Government, Private, Midmarket.
Source type Government
  • Then click ok, and you get a drop-down list at a selected cell.
excel countif example 2.8
  • 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.
excel countif example 2.9

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 could 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.

excel countif example 3.1
  • 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.
count of government and private

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 the plus (+) symbol and start one more COUNTIF function and count Private word in this COUNTIF function.
excel countif example 3.3
  • Click Enter, and you will get the answer.
excel countif example 3.4

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:

The 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.
excel countif example 3.5
  • In the criteria, the argument opens a curly bracket and supply two criteria instead of regular one criteria.
excel countif example 3.6
  • Now here, COUNTIF alone cannot give me the result here. Since we are adding up two values here, we need to apply the SUM function before the COUNTIF function.
excel countif example 3.7
  • Now hit enter this will give you the total count of Government & Private.
excel countif example 3.8

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.
excel countif example 4.1
  • In order to count the numbers which are greater than 20, open the COUTNIF function and select the range first.
excel countif example 4.2
  • In the criteria, firstly mention the greater than symbol (>) in double-quotes.
greater than symbol
  • Now supply the criteria number with ampersand (&) symbol.
criteria number with ampersand (&) symbol
  • We have got the total count of numbers which are greater than 20.
count how many numbers are greater then 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

how to count values
  • 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 the below formula to get the total.
count values not equal to government
  • The total count of the words which are not equal to the word Government is 4.
total count of words

Recommended Articles

This has been a guide to COUNTIF Examples in Excel. Here we discuss basic and advanced examples of COUNTIF Function in excel along with downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>