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.

But the function COUNTIF accepts only one criteria of selection. If you want to count your row for more than one criteria, it's better for you to use the function COUNTIFS.

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, criteria of selection)

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

 

 

 

 

COUNTIF with greater than / smaller than

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.

Related posts


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


3 comments

    • dr.strange on 06/03/2018 at 04:28
    • Reply

    i was expecting a quiz

    • Jean on 09/01/2018 at 19:15
    • Reply

    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. Thanks
      And share the links of the website during your training

Leave a Reply

Your email address will not be published.