The COUNTIF function in excel is used to count cells that meet a single criteria. The COUNTIF Function was introduced to excel in the year 2000, and although it is a little inferior to its successor (the COUNTIFS Function), it still serves a purpose which is ease of deployment as a result of fewer arguments compared to the COUNTIFS Function.
To recap, the COUNTIF Function is used to count numbers that meet a single condition whilst the COUNTIFS Function can be used to count numbers that meet multiple conditions. Our focus for this post is the COUNTIF Function, we would take you through all you need to know about this function. For the COUNTIFS function please check our guide here.
Syntax & Argument
The Syntax for the COUNTIF Function is as follows:
=COUNTIF(range,criteria)
In the introduction to this post, we stated that the COUNTIF Function is used for counting numbers that meet a single condition, and that is why we have a single pair of range and criteria arguments, and both are required for the COUNTIF Function to work properly.
Range: This is the range of cells to count.
Criteria: Condition that determines the cells that would be counted.
Now, let’s move on to things to note before using the COUNTIF Function.
Usage Notes
The below are the usage notes for the COUNTIF Function
- The COUNTIF Function can be used to count cells containing dates, numbers and text
- COUNTIF is restricted to checking for a single criteria. To apply a check to multiple criteria, you would need to use the COUNTIFS Function.
- Criteria can include logical operators such as (>, <, <>, <=, >=)
- To use the COUNTIF Function to count cells meeting a logical test for numbers, with the number itself being in a separate cell, you would need to include an ampersand (&) character to activate that input cell.
Defining the Criteria
In the section above, we discussed using logical operators to define the criteria for the COUNTIF Function, as well as using a combination of logical operators and ampersand to select the criteria from a separate cell. In this section, we would show the syntax for the various types of criteria.
| Objective | Criteria |
|---|---|
| Cells greater than 50 | “>50” |
| Cells less than 100 | “<100” |
| Cells equal to 75 | 75 or “75” |
| Cells greater than or equal to 200 | “>=200” |
| Cells less than or equal to 150 | “<=150” |
| Cells equal to Blue | “Blue” |
| Cells not equal to Green | “<>”red |
| Cells that are blank | “” |
| Cells that are not blank | “<>” |
| Cells that begin with T | “T*” |
| Cells greater than B5 | “>”&B5 |
| Cells Less than Now | “<“&NOW() |
Our last usage note in the previous section is illustrated with the last two examples in this section (cells greater than B5 and cells less than Now). As stated previously, you would need the ampersand character anytime you want to reference a value from another cell. This is because excel needs to evaluate the cell references and then return a value which would be joined with the logical operator to test whether true or not.
Examples of COUNTIF Function
We have the sales values for a company in its various cities of operation. With this dataset, we would illustrate how to deploy the COUNTIF function in various use cases.
Basic Example
There are some conditions we need to check the number of occurence for, these are:
- Sales greater than $5,000
- Sales in California
- Sales in Oregon
To achieve the above, we use the formulas below:
=COUNTIF(D4:D17,”CA”) // Count cells with sales in Californa State
=COUNTIF(D4:D17,”OR”) // Count cells with sales in Oregon State
Value from another cell
Now, let our criteria be contained in a cell we can refer to in the formula. For example, assuming we have the value $6,000 in cell G7, and we need to return the count of locations with sales value greater than or equal to $6,000. To do that, we use the formula below:
Not equal to
For this section, we would be returning the count of cells with sales not in California. Our formula would be as follows:
Dates
We have a new data set for this section and the sections to follow. Our data set contains companywide order information for our business, and we would be showing you how to return count based on certain criteria.
As you can see from the above image, there are two examples on dates. The first would be done by selecting the date criteria from another cell whilst the second would be done by partially hardcoding the date within our COUNTIF formula. Let’s start with returning the count of orders with most recent order date greater than the 15th of June 2022.
The formula below will return the count of orders with most recent order date greater than K10
We can also partially hardcode the criteria date by combining with the DATE function. In our example, we need to return the count of dates less than June 20, 2022 and the formula below should do the trick:
Blank Cells
With the COUNTIF Function, you can also count cells that are either blank or not blank. To begin, let’s count the number of cells with order value not blank. We would use the formula below to achieve this:
Moving on to cells with order values that are blank, we would use the formula below to return the count:
Wildcards
Wildcards can be used to count cells with similar features, for example cells having a few characters in common. The Wildcard characters are asterisk (*), question mark (??) and tilde (~). The question mark matches the number of characters in a specified position whilst the asterisk (*) matches any sequence of characters regardless of its length. For example, there are customer numbers that have the characters MBSS within them, and we need to return the count of such customer numbers.
To return the count of customer numbers having the characters MBSS, we use the formula below:
OR Logic
Our final section is on combining the COUNTIF with the OR function to test for multiple criteria. In our example we would count the number of customers that are either in the Gold category or Platinum category. Because excel would check and count for each criteria, we would need to use the SUM function to return a single value for both criteria.
