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:

oztraining_728x90

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

Cleaning Data with Excel’s PROPER Formula   What does it do? Capitalizes the first letter in a text string and any other letters in the text that follow a space. Converts all other letters to lowercase letters. Formula breakdown: =PROPER(text) What it means: =PROPER(this text cell) There are...
Getting the remainder with Excel’s MOD Formu... What does it do? Gives you the remainder after dividing one number with another Formula breakdown: =MOD(number1, number2) What it means: =MOD(a number, divided by this number) In Excel it is very easy to divide two numbers. But how about if you need to ge...
Cleaning Data with Excel’s SUBSTITUTE Formul... 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, ) When you needed to replace a specific text...
Division Formula in Excel What does it do? Divides two numbers Formula breakdown: =number1 / number2 What it means: =the number being divided / the number you are dividing by In Excel dividing numbers together is really easy! The Division Formula is done through the use of the div...