↑ Return to Condition

COUNTIF

When you are trying to count the number of times you have a name, a date, a value in a list, only the function COUNTIF should be used.

Presentation

The function COUNTIF requires only two parameters

  • the range of cells where is your data
  • the value you search.
=COUNTIF(Range of cells, research)

 

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 the exercise below by writing the formula in B15 to find the number of Post-it present in column B (enter your formula directly into the following spreadsheet)

 

 

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, see the page Function IF.

 

For example, if you want to know the number of order 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, even if previously we don't put any symbol in our formula, you can also write your formula with the symbol =. For instance you can write the first formula like this and it works !

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

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

3 comments

  1. vicky

    awww the exercises isnt working

    1. Frédéric LE GUEN

      It works now. Thanks for send me a message

  2. Anonymous

    Helpful post. Hope you can add more with increase in level of difficulty. :)

Leave a Reply