What is the Excel COUNTIF Function?
The COUNTIF Function in Excel counts the number of cells that meet a specified criteria, i.e. a condition.
Table of Contents
- What is the Excel COUNTIF Function?
- How to Use COUNTIF Function in Excel?
- Excel COUNTIF Function Formula
- Numeric Criterion Syntax: Logical Operator
- Text Strings, Date, Blank and Non-Blank Criterion
- Wildcards in Criterion
- COUNTIF Function Calculator – Excel Model Template
- Part 1. Numeric Criteria COUNTIF Function Examples
- Part 2. Text Strings COUNTIF Function Examples
How to Use COUNTIF Function in Excel?
The Excel “COUNTIF” function is used to count the number of cells in a selected range that meets a specific condition.
Given one criterion, the COUNTIF function searches for an exact match to determine the total number of cells under which the condition is met.
For instance, the criteria could be related to finding the number of cells with values greater than, less than, or equal to a specific value.
The primary drawback to the “COUNTIF” function is that only one condition is supported. If the criteria in question consists of multiple conditions, the “COUNTIFS” function would be the more practical alternative.
In addition, the criterion is not case-sensitive, so the use of upper or lower case spelling in the text string does not affect the outcome.
Excel COUNTIF Function Formula
The formula for using the COUNTIF function in Excel is as follows.
=COUNTIF(range, criterion)
- Range → The selected range containing the data set across which the function will search for the cells that match the stated criteria.
- Criterion → The specific condition that must be met in order for the function to count the cell.
Numeric Criterion Syntax: Logical Operator
The range can contain text strings and numbers, whereas the criterion most often contains a logical operator such as:
| Logical Operator | Description |
|---|---|
| > | Greater Than |
| < | Less Than |
| = | Equal To |
| >= | Greater Than or Equal To |
| <= | Less Than or Equal To |
| <> | Not Equal To |
Text Strings, Date, Blank and Non-Blank Criterion
For text or date-based conditions, it is necessary to enclose the criterion in double quotes, otherwise the formula will not work.
| Criterion | Description |
|---|---|
| Text |
|
| Date |
|
| Blank Cells |
|
| Non-Blank Cells |
|
| Cell References |
|
Wildcards in Criterion
The term “wildcards” refer to special characters such as a question mark, asterisk, or tilde.
| Wildcard | Description |
|---|---|
| (?) |
|
| (*) |
|
| (~) |
|
COUNTIF Function Calculator – Excel Model Template
We’ll now move on to a modeling exercise, which you can access by filling out the form below.
You’re logged in! Form submission not required. Access your file below.
Part 1. Numeric Criteria COUNTIF Function Examples
Suppose we’re given the following range of numeric data to count the number of cells that meet various types of conditions.
The range is on the left column, while the condition is on the right column.
| Range | Condition |
|---|---|
| 10 | Equal to 10 |
| 12 | Greater Than 10 |
| 15 | Less Than 10 |
| 14 | Greater Than or Equal to 10 |
| 6 | Less Than or Equal to 10 |
| 8 | Not Equal to 10 |
| 12 | Blank Cells |
| 10 | Non-Blank Cells |
The COUNTIF equations that we’ll use to count the matching cells are the following:
=COUNTIF($B$6:$B$13,10) → Count = 2
=COUNTIF($B$6:$B$13,">10") → Count = 4
=COUNTIF($B$6:$B$13,"<10") → Count = 2
=COUNTIF($B$6:$B$13,">=10") → Count = 6
=COUNTIF($B$6:$B$13,"<=10") → Count = 4
=COUNTIF($B$6:$B$13,"<>10") → Count = 6
=COUNTIF($B$6:$B$13,"") → Count = 0
=COUNTIF($B$6:$B$13,"<>") → Count = 8
Part 2. Text Strings COUNTIF Function Examples
In the next section, we’ll work with the following data set of text strings, which are cities in this case.
| Range | Condition |
|---|---|
| New York City | Equal to Austin |
| Austin | Ends in “n” |
| Boston | Starts with “s” |
| San Francisco | Contains Five Characters |
| Los Angeles | Contains Space in Between |
| Miami | Contains Text |
| Seattle | Contains “City” |
| Chicago | Not Miami |
The COUNTIF function equations that we’ll enter into Excel to count the cells that meet each of the corresponding criteria are the following:
=COUNTIF($B$17:$B$24,"=Austin") → Count = 1
=COUNTIF($B$17:$B$24,"*n") → Count = 2
=COUNTIF($B$17:$B$24,"s*") → Count = 2
=COUNTIF($B$17:$B$24,"??????") → Count = 2
=COUNTIF($B$17:$B$24,"* *") → Count = 3
=COUNTIF($B$17:$B$24,"*") → Count = 8
=COUNTIF($B$17:$B$24,"City") → Count = 1
=COUNTIF($B$17:$B$24,"<>Miami") → Count = 7
No comments yet.