# 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

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

Thanks