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.

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

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

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

## 2 comments

## Jean

09/01/2018 at 19:15 (UTC 1) Link to this comment

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

## Frédéric LE GUEN

09/01/2018 at 19:27 (UTC 1) Link to this comment

Thanks

And share the links of the website during your training