Home » Function » Condition » Function COUNTIF

# Function COUNTIF

Reading time: 2 minutes
Last Updated on 07/06/2023 by Frédéric LE GUEN

A basic task with Excel is to count the number of rows for a specific criteria. The function COUNTIF do that easily 😃

## Don't use the filters

Most of the Excel users, use the filter to count the rows.

But this solution is really not convenient when you have to repeat this action for many values.

The solution is to use COUNTIF or COUNTIFS

## Presentation of COUNTIF

The function COUNTIF counts the number of rows for one criteria.

The function requires only two arguments

• The range of cells where is your data
• The value you search (the criterion).

=COUNTIF(Range of cells, criteria of selection)

## Example with COUNTIF

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

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

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

=COUNTIF(B3:B12,"Banana")

And the result is

## Replace the criteria by a cell reference

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 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 function COUNTIF, 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

## More than one criteria

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

#### 3 Comments

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

i was expecting a quiz

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

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

Thanks
And share the links of the website during your training

# Function COUNTIF

Reading time: 2 minutes
Last Updated on 07/06/2023 by Frédéric LE GUEN

A basic task with Excel is to count the number of rows for a specific criteria. The function COUNTIF do that easily 😃

## Don't use the filters

Most of the Excel users, use the filter to count the rows.

But this solution is really not convenient when you have to repeat this action for many values.

The solution is to use COUNTIF or COUNTIFS

## Presentation of COUNTIF

The function COUNTIF counts the number of rows for one criteria.

The function requires only two arguments

• The range of cells where is your data
• The value you search (the criterion).

=COUNTIF(Range of cells, criteria of selection)

## Example with COUNTIF

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

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

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

=COUNTIF(B3:B12,"Banana")

And the result is

## Replace the criteria by a cell reference

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 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 function COUNTIF, 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

## More than one criteria

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

#### 3 Comments

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

i was expecting a quiz

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

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

Thanks
And share the links of the website during your training

{"mobile_concate":{"mobile-menu":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/mobile-menu.min.css"},"tf_base":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/base.min.css","v":"7.1.1"},"themify_common":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/themify.common.min.css","v":"7.1.1"},"builder-styles-css":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/themify-builder-style.min.css","v":"7.1.1"},"tb_tbp":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/plugins\/themify-builder-pro\/public\/css\/tbp-style.min.css","v":"3.0.8"},"tf_theme_site-logo":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/styles\/modules\/site-logo.min.css","v":"7.1.1"},"tf_theme_site-description":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/styles\/modules\/site-description.min.css","v":"7.1.1"},"tf_search_form":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/modules\/search-form.min.css","v":"7.1.1"},"tb_image":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/image.min.css","v":"7.1.1"},"tb_image_top":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/image_styles\/top.min.css","v":"7.1.1"},"tb_divider":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/divider.min.css","v":"7.1.1"},"tb_post-content":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/text.min.css","v":"7.1.1"},"wp-block-library":{"s":"https:\/\/www.excel-exercise.com\/wp-includes\/css\/dist\/block-library\/style.min.css","v":"6.2"},"tb_comments":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/plugins\/themify-builder-pro\/public\/css\/modules\/comments.min.css","v":"3.0.8"},"tf_theme_social_links":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/css\/modules\/social-links.min.css","v":"7.1.1"},"tb_text":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/themify\/themify-builder\/css\/modules\/text.min.css","v":"7.1.1"},"theme-style":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/style.min.css","v":"7.1.1"},"themify-media-queries":{"s":"https:\/\/www.excel-exercise.com\/wp-content\/themes\/themify-infinite\/media-queries.min.css","v":"7.1.1","m":"(max-width:1200px)"}}