COUNTIF

What does this function do?

The COUNTIF function in Excel is used to count the number of cells in a range that meet a specific criterion. COUNTIF is very useful when you need to know how many times a particular value appears in a dataset. This can significantly simplify the process of working with large datasets in Excel.

Function syntax

=COUNTIF(range, criteria)

range: This is the range of cells you want to check.
criteria: This is the condition that cells in the range must meet. The criterion can be a number, text expression, cell reference, or formula.

COUNTIF criteria and operators

The following objects can serve as criteria for the COUNTIF function in Excel:

• Directly specified values in various formats:
  • Numbers: Direct number specification, e.g., 10 or 3.14.
  • Text strings: Any text values enclosed in quotes, e.g., “Word” or “Text”.
  • Logical values: TRUE or FALSE.
  • Dates: Can use dates in a format that Excel recognizes as dates, e.g., “2024-01-01” or DATE(2024, 1, 1).
  • Percentages, e.g., “25%”, “>50%”
• Cells: Reference to a cell containing the criterion. For example, if the criterion is in cell A1, you can specify A1 as the criterion.
• Cell ranges (then the function returns an array equal to the range specified as the criterion).
• Formulas: The criterion can be specified by a formula whose result is used to compare with cells in the range.

It’s important to note that the criterion must be specified according to the data type in the analyzed range for the COUNTIF function to work correctly.

COUNTIF function operators

Operators are conditionally divided into numeric and text. Note that COUNTIF:

• can use numeric comparison operators for text (e.g., B > A, and BB > BA)
• but cannot use text operators for numbers (e.g., 200 is contained in 2002 as text, but if the value format is not text, it will return 0)

In the second case, you might find the TEXT function useful for converting numbers to text format.

Numeric operators

1. Greater than (>): Counts cells containing values greater than specified.
2. Less than (<): Counts cells containing values less than specified.
3. Greater than or equal to (>=): Counts cells containing values greater than or equal to specified.
4. Less than or equal to (<=): Counts cells containing values less than or equal to specified.
5. Equal to (=): Counts cells strictly equal to the specified value. Usually the “=” operator is omitted as it’s implied by default.
6. Not equal to (<>): Counts cells that are not equal to the specified value.

These operators are used in combination with a numeric value or cell reference as the criterion to count the number of cells meeting a specific condition.

Text operators

The COUNTIF function also supports text operators (also known as wildcard characters). They allow counting cells by pattern (professional slang also includes the expression “by mask“). Text operators work with numeric values too. There are two of them:

• Question mark (?): Used in criteria to replace one any character. For example, the criterion “a?” will match any two-letter text starting with “a”.
• Asterisk (*): Used to replace any sequence of characters, including their absence. For example, the criterion “a*” will match any text starting with “a” or equal to “a“.

“?*” or “*?” as a criterion will count cells containing text characters in the range, since text criteria won’t work on numeric and logical values.

Function usage examples

Let’s examine the formulas at the top of the page in detail. We have an electronics online store product catalog with their categories and prices. Our task is to determine the number of items meeting certain numeric or text criteria.

Examples with text criteria

Suppose we need to count products with a specific brand in their name. Then we’ll need to use the following formulas:

The formulas themselves are in cells C14 and C15. For easy copying, here they are:

=COUNTIF(A2:A11,"*Samsung*")

=COUNTIF(A2:A11,"*"&B15&"*")

The difference between the second and first formulas is that the second one doesn’t need editing – you just change the value in the cell it depends on. It’s even more convenient if the influencing cell has a dropdown list of brands. The asterisks in the formula allow searching text by inclusion, not exact match.

When searching by exact match of cells, for example by product category, operators are not required. By default, the search is performed by exact match.

Corresponding formulas:

=COUNTIF(B2:B11,"Accessories")

=COUNTIF(B2:B11,B17)

Here the second formula differs from the first similarly to the previous example.

Examples with numeric criteria

When you need to count cells meeting certain numeric criteria, the main feature is that, although they are numeric, they are specified inside quotes as a string parameter.

Below are the corresponding formulas for easy copying.

1. =COUNTIF(C2:C11,">3000")

2. =COUNTIF(C2:C11,">"&B19)

3. =SUM(--(ABS(D2:D11/C2:C11-1)>20%))

Note the third formula. This is an array formula, and it’s used here because the COUNTIF function doesn’t work with arrays. That is, you cannot divide values from two ranges by each other and compare them with the target discount value in an array using COUNTIF.

The absolute discount value (returned by the ABS function) is compared with the target value of 20%, and the array consisting of logical values TRUE and FALSE is converted to zeros and ones using the double negation method. Then the SUM function returns the sum of ones.

4. =COUNTIF(E2:E11,"*99")

The fourth formula also has its peculiarity. If we applied it to numeric cells in column D, it would return 0. Therefore, we needed an additional column where the TEXT function converted numbers to text format.

More examples

Want more examples of using the function? Below is a list of articles on this site where the COUNTIF function is used as auxiliary in combination with others:

Count words in Excel cells

Count cells with numbers

N-gram analysis in Excel

Find duplicate values in a range

This post is also available in RU.