«

»

Function COUNTIF

If you need to count the number of rows corresponding to a name or a product, there is better than to use the filter. In fact, the COUNTIF function is dedicated for this.

Presentation of COUNTIF

The function COUNTIF requires only two parameters

  • the range of cells where is your data
  • the value you search (the criterion).

=COUNTIF(Range of cells, criterion)

Application example

In the following table, you have a shopping list of equipment and want to know how often you sale CD-ROM.

 

 

 

 

 

 

 

As you can see in the picture, we have 4 times the word "CD-Rom", it is this value that we want to display in B15. Then, we will write the following formula

=COUNTIF(B3: B12,"CD-Rom")
 

 

 

 

 

And the result is

 

 

 

 

Now if instead of typing the name of the data you want to have the reference of a cell where its value = the name you want.

For instance, you can use the cell B3 where its value is CD-Rom

=COUNTIF(B3:B12,B3)
 

 

 

 

It is imperative that the value you look for is always the same. In this example, the second word "CD-Rom" begins with a space (so it's not exactly the same word). This is why, in this example, the formula returns only 3 times the word "CD-Rom".

On the other hand, the COUNTIF function ignores sensitive case

 

 

 

 

 

Practice in a live worksheet

You can copy the previous formula in this worksheet to see the result in the red cells.

Greater than / smaller than a value

If you want to count the number of elements larger (or smaller) than a certain value, just as the second parameter to write a logical test (such as >100, >=100, <50, ...)

For all logical tests and how to use it, see this article.

For example, if you want to know the number of order with a quantity greater than 80 units, you will write in cell B15 the formula

=COUNTIF(C3: C12, ">=80")

 

 

As you can see, the test is between double-quote like a text. Why ? 🤔🤨😕

Well, previously we don't put any logical symbol in our formula. But you can also write the same formula with the symbol = like this

=COUNTIF(B3: B12,"=CD-ROM")

So, for any test in a function COUNTIF, you write it between double-quote.

More than one criteria

The COUNTIF function can be used for a single criterion in a single column. If you want to count the number of rows corresponding on a multi-selection of more than one column, you better used the COUNTIFS function 

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/countif/


3 comments

  1. dr.strange

    i was expecting a quiz

  2. Jean

    I really appreciate your practice exercises, they have been very helpful. I teach seniors how to use both Word/Excel and you exercises have been good.

    Jean Brown
    Dr. Piper Center

    Jean

    1. Frédéric LE GUEN

      Thanks
      And share the links of the website during your training

Leave a Reply

Your email address will not be published. Required fields are marked *