Function COUNTIF in Excel

Function COUNTIF in Excel
Last Updated on 21/08/2023
Reading time: 2 minutes

The function COUNTIF counts the number of rows for one criterion

  1. The column containing the criterion

    Select the column where is the criterion to select

  2. The criterion

    Value of the criterion or reference of a cell

=COUNTIF(Range of cells, criteria of selection)

Don't use the filters

Most of the Excel users, use the filter to count the rows. But this solution is really not efficient when you have to do this, again and again, for many values.

Presentation of the filter in the header

The solution is to use the COUNTIF function or the COUNTIFS function

How to use COUNTIF

In the following document, you have a list of sales.

How to count the number of time we have a Banana

You want to know how many times you have sold Banana.

To return the number of times you have the name Banana in your list, you have to write the formula like this

=COUNTIF(B3:B12,"Banana")

And the result is

COUNTIF returns the number of time we have a Banana

Replace the criteria with a cell.

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 cell G4 where its value is Banana

=COUNTIF(B3:B12,G4)

Not sensitive case

Don't worry if your criterion doesn't have the same case of the data in your table, the COUNTIF function of Excel, is not a sensitive case.

For instance, in this example, we want to count the number of times we have the word PEACH in uppercase and the result is

The criterion could be a cell with the COUNTIF function of Excel

More than one criteria

If you need to count rows with more than one criterion you must use the function COUNTIFS. It isn't more complex but you just have to write more arguments 😉

COUNTIFS of Excel with 2 criteria

3 Comments

  1. dr.strange
    06/03/2018 @ 04:28

    i was expecting a quiz

    Reply

  2. Jean
    09/01/2018 @ 19:15

    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

    Reply

    • Frédéric LE GUEN
      09/01/2018 @ 19:27

      Thanks
      And share the links of the website during your training

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Function COUNTIF in Excel

Reading time: 2 minutes
Last Updated on 21/08/2023

The function COUNTIF counts the number of rows for one criterion

  1. The column containing the criterion

    Select the column where is the criterion to select

  2. The criterion

    Value of the criterion or reference of a cell

=COUNTIF(Range of cells, criteria of selection)

Don't use the filters

Most of the Excel users, use the filter to count the rows. But this solution is really not efficient when you have to do this, again and again, for many values.

Presentation of the filter in the header

The solution is to use the COUNTIF function or the COUNTIFS function

How to use COUNTIF

In the following document, you have a list of sales.

How to count the number of time we have a Banana

You want to know how many times you have sold Banana.

To return the number of times you have the name Banana in your list, you have to write the formula like this

=COUNTIF(B3:B12,"Banana")

And the result is

COUNTIF returns the number of time we have a Banana

Replace the criteria with a cell.

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 cell G4 where its value is Banana

=COUNTIF(B3:B12,G4)

Not sensitive case

Don't worry if your criterion doesn't have the same case of the data in your table, the COUNTIF function of Excel, is not a sensitive case.

For instance, in this example, we want to count the number of times we have the word PEACH in uppercase and the result is

The criterion could be a cell with the COUNTIF function of Excel

More than one criteria

If you need to count rows with more than one criterion you must use the function COUNTIFS. It isn't more complex but you just have to write more arguments 😉

COUNTIFS of Excel with 2 criteria

3 Comments

  1. dr.strange
    06/03/2018 @ 04:28

    i was expecting a quiz

    Reply

  2. Jean
    09/01/2018 @ 19:15

    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

    Reply

    • Frédéric LE GUEN
      09/01/2018 @ 19:27

      Thanks
      And share the links of the website during your training

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *