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

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

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...
Count Formula in Excel What does it do?Counts the number of cells that contain numbersFormula breakdown:=COUNT(value1, ...)What it means:=COUNT(range of cells to check, ...)Ever had a column of data and wanted to check if all of the values contain valid numbers?It would ...
Cleaning Data with Excel’s CLEAN Formula  What does it do?Removes all nonprintable characters from textFormula breakdown:=CLEAN(text)What it means:=CLEAN(this dirty text cell)There are times when imported text from other applications contain characters that are unprintable. The CLEAN...
RANDBETWEEN Function for Excel Dates What does it do?Generates random dates, in between a start date and an end dateFormula breakdown:=RANDBETWEEN(bottom, top)What it means:=RANDBETWEEN(starting date for random date generation, end date for random date generation) If you need to ...