The COUNTIF function is incredibly useful when you want to count things like overdue invoices, employees in Engineering, or orders over $100. Learn how to use it in this short video.
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.
COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,...
Download 200+ Excel Shortcuts
Thanks a lot, this is perfectly working and saving me hours. - Sven