What does it do?

Counts the number of cells that matches your specified condition

Formula breakdown:

=COUNTIF(range, criteria)

What it means:

=COUNTIF(range of cells to check, condition to check against)


Do you have a scenario where you want to count the number of cells that match a specific condition?

I’m sure you do!  There is a simple way to count this with Excel’s COUNTIF formula!

The COUNTIF formula is very flexible indeed, so let us try to count the following from our Excel worksheet:

  • Number of cells greater than 2
  • Number of cells that have a Yellow value
  • Number of cells that start with the letter J

CountIf Formula in Excel

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the COUNTIF function in a blank cell:

=COUNTIF(

CountIf Formula in Excel

 

STEP 2: The COUNTIF arguments:

range

What is the range of values that you want to check against your condition?

=COUNTIF(A9:A12,

CountIf Formula in Excel

criteria

What is the condition that you want to check against?

For our 1st example, we want to count the number of values greater than 2.

=COUNTIF(A9:A12, “>2”)

CountIf Formula in Excel

You now have your count of numbers greater than 2!

CountIf Formula in Excel

 

STEP 3: Now let us try for counting the number of Yellow values:

=COUNTIF(C9:C12, “Yellow”)

CountIf Formula in Excel

You now have your count of values that have the Yellow text!

CountIf Formula in Excel

 

STEP 4: Now let us try for counting the number of names starting with the Letter J:

Let us use the wildcard expression J*

* signifies a wildcard character i.e. Return any value that begins with a J

=COUNTIF(E9:E12, “J*”)

CountIf Formula

You now have your count of values that have a starting letter of J!

CountIf Formula in Excel

 

CountIf Formula in Excel

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

CountBlank Formula in Excel What does it do? Counts the number of cells that are blank Formula breakdown: =COUNTBLANK(range) What it means: =COUNTBLANK(range of cells to check) Do you have a scenario where you want to count the number of cells that are blank in your Excel data? If y...
Cleaning Data with Excel’s REPLACE Formula What does it do? Replaces part of a text string, based on the number of characters you specify, with a different text string Formula breakdown: =REPLACE(old_text, start_num, num_chars, new_text) What it means: =REPLACE(this cell, starting from this number, all the ...
Remove Second Hyphen with Excel’s SUBSTITUTE... What does it do? Substitutes new_text for old_text in a text string. Formula breakdown: =SUBSTITUTE(text, old_text, new_text, ) What it means: =SUBSTITUTE(This cell, By this text character, To this text character, ) There are times where I needed to remove t...
Extracting Data with Excel’s RIGHT Formula What does it do? It returns the last character or characters in a text string, based on the number of characters you specify. Formula breakdown: =RIGHT(text, ) What it means: =RIGHT(look in this cell, extract X characters) There are times when you will need ...