Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to use the COUNTIF function

Tags 
Video Transcript 

In this video, we'll look at How to use the COUNTIF function to count cells that meet a single criteria.

Let's take a look.

The COUNTIF function counts cells that satisfy a single condition that you supply. It takes 2 arguments, range and criteria.

For example, if I want to count the cells in this range that contain the number 15, I enter B7:B12 for the range, and 15 for the criteria. Excel then returns 1, since only one cell contains 15.

If I temporarily enter another 15, that result will change.

You can add logical operators to the criteria. To count cells with a value greater than 15, I enter a criteria of ">15". When the criteria contains a logical operator, you'll need to enclose it in double quotes.

You can use COUNTIF with both text and numbers. To count the number of cells that contain "apple", the criteria is simply "apple" in double quotes. Note that COUNTIF is not case-sensitive.

You can use empty double quotes to count blanks.

COUNTIF also supports wildcards. P plus an asterisk will return 3, since 3 entries begin with a "p".

You can use COUNTIF to count dates that meet one condition as well.

To count dates greater than Jan 1, 2013, enter the greater than operator and the full date in double quotes.

Follow the same process to count dates less than Jan 1, 2012.

Because dates appear in different formats in many parts of the world, a safer option is to use the DATE function in your criteria. This will ensure that Excel always recognizes the date correctly.

To use DATE, you'll need to concatenate the operator and the date function together in the criteria.

Note that F15 and F16 return the same result.

Also, using the date function makes it easier to pull in the month, date, and year from other cells in the worksheet.

You an also move the date out onto the worksheet where it can be easily changed.

Author 
Dave Bruns
Thank you, and thank you so very much for your videos, they are sooooo helpful. - Elizabeth
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course