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.

Sommaire

## 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")

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-quotelike 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

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

## 3 comments

i was expecting a quiz

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

Author

Thanks

And share the links of the website during your training