Summary

The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells that contain dates, numbers, and text. Criteria can include logical operators (>,<,<>,=) and wildcards (*,?).

Purpose

Count cells that match criteria

Return value

A number representing cells counted.

Arguments

• range - The range of cells to count.
• criteria - The criteria that controls which cells should be counted.

Syntax

=COUNTIF(range, criteria)

How to use

To use COUNTIF, provide a range that contains cells you want to count, then provide the criteria needed to apply the condition. For example, in the worksheet shown, to count numbers greater than 100 in the range D5:D16, you can use COUNTIF like this:

``=COUNTIF(D5:D16,">100") // returns 6``

COUNTIF returns 6, since there are 6 cells in D5:D16 that contain numbers larger than 100. Similarly, to count cells in the range B5:B16 that contain the text string "Jim", you can use a formula like this:

``=COUNTIF(B5:B16,"jim") // returns 4``

COUNTIF returns 4, since there are 4 cells in B5:B16 that contain "Jim". Notice that COUNTIF is not case-sensitive. You can use "Jim" or "jim" for criteria and COUNTIF will return the same result. Also, note that the word "contains" in this case means "equals". To count sales in California (abbreviated as "CA") in the range C5:C16 you can use COUNTIF like this:

``=COUNTIF(C5:C16,"CA") // returns 2``

COUNTIF returns 2 since there are 2 instances of "CA" in the range C5:C16. Again, you can use "CA" or "ca" for the criteria with the same result.

Note that the syntax for the criteria argument in COUNTIF is somewhat unique in Excel because you typically need to enclose the criteria in double quotes (""), especially when using operators. For example, instead of simply entering >100 as the criteria, you must enter ">100" in double quotes. If you don't quote values as required, Excel will not let you enter the formula. See the next section for more details and examples of the syntax required to apply various criteria.

Applying criteria

The COUNTIF function supports logical operators (>,<,<>,<=,>=) and wildcards (*,?) for partial matching. The tricky part about using the COUNTIF function is the syntax used to apply criteria. The table below shows examples of the syntax needed for many common criteria:

Target Criteria
Cells greater than 75 ">75"
Cells equal to 100 100 or "100"
Cells less than or equal to 100 "<=100"
Cells equal to "Red" "red"
Cells not equal to "Red" "<>red"
Cells that are blank "" ""
Cells that are not blank "<>"
Cells that begin with "X" "x*"
Cells less than A1 "<"&A1
Cells less than today "<"&TODAY()

Notice the last two examples involve concatenation with the ampersand (&) character.  Any time you are using a value from another cell, or using the result of a formula in criteria with a logical operator like "<", you will need to concatenate. This is because Excel needs to evaluate cell references and formulas first to get a value, before that value can be joined to an operator.

COUNTIF shares this unusual syntax for entering criteria with seven other functions.

Double quotes ("") in criteria

In general, text values need to be enclosed in double quotes (""), and numbers do not. However, when a logical operator is included with a number, both the number and operator must be enclosed in quotes, as seen in the second example below:

``````=COUNTIF(range,100) // count cells equal to 100
=COUNTIF(range,">32") // count cells greater than 32
=COUNTIF(range,"jim") // count cells equal to "jim"
``````

Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, COUNTIF will count values in a given range that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.

``````=COUNTIF(range,"<"&B1) // less than B1
``````

Not equal to

To construct "not equal to" criteria, use the "<>" operator surrounded by double quotes (""). For example, the formula below will count cells not equal to "red" in a given range:

``````=COUNTIF(range,"<>red") // not "red"
``````

For more details, see Count cells not equal to.

Blank cells

COUNTIF can be configured to count cells that are blank or not blank in a given range as seen below:

``````=COUNTIF(range,"") // count blank
=COUNTIF(range,"<>") // count not blank

``````

Note: COUNTIF treats formulas that return an empty string ("") as not blank. This can cause trouble when counting the results from other formulas. See this page for some workarounds for this problem.

Dates

The best way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, with a date in cell B1, you can count dates in a given range that are greater than B1 like this:

``````=COUNTIF(range, ">"&B1) // count dates greater than B1
``````

Notice we must concatenate an operator to the date in B1. To use more advanced date criteria (i.e. to count dates between two dates) you'll want to switch to the COUNTIFS function, which can handle multiple criteria. If you want to hardcode a date into COUNTIF, the safest way is to use the DATE function, because this guarantees Excel will interpret the date correctly. For example, to count dates less than April 1, 2020, you can use the DATE function with COUNTIF like this:

``````=COUNTIF(range,"<"&DATE(2020,4,1)) // dates less than 1-Apr-2020
``````
Pro-tip: In general, you should avoid hardcoding a date into a formula. Instead, put the date into a cell, then reference that cell in your formula. This makes the worksheet more transparent since you can easily see the date being used, and change the date without editing the formula.

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count text strings that contain the text "apple", you can use a formula like this:

``````=COUNTIF(range,"*apple*") // cells that contain "apple"
``````

To count cells that contain any 3 text characters, you can use a formula like this:

``````=COUNTIF(range,"???") // cells that contain any 3 characters
``````

Note: wildcards only work with text values. The formula above will not count a 3-digit number like 123 or 436.

The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?) in cells, you can use "~?" for criteria like this:

``=COUNTIF(range,"~?")``

To count text strings that end in a question mark (?), you can use the following:

``=COUNTIF(range,"*~?")``

Similarly, you can count asterisks(*) with "~*", and count tildes (~) "~~".

For more details on counting cells that contain text, see Count cells that contain specific text.

OR logic

The COUNTIF function is designed to apply just one condition, so there is no obvious way to count cells that are "this OR that". However, one workaround is to provide criteria as an array constant like {"red","blue} for criteria, and then nest the COUNTIF formula inside the SUM function like this:

``````=SUM(COUNTIF(range,{"red","blue"})) // red or blue
``````

The formula above will count cells in range that contain "red" or "blue". Essentially, COUNTIF returns two counts in an array (one for "red" and one for "blue") and the SUM function returns the sum of the two counts. For more information, see this example.

Limitations

The COUNTIF function has some limitations you should be aware of:

• COUNTIF only supports a single condition. To count cells that meet multiple conditions, use the COUNTIFS function.
• COUNTIF requires an actual range for the range argument; you can't provide an array. This means you can't alter values in a range inside your formula before applying criteria.
• COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.

The most common way to work around the limitations above is to use the SUMPRODUCT function. In the current version of Excel, another option is to use the newer BYROW and BYCOL functions.

Notes

• Text strings in criteria must be enclosed in double quotes (""), i.e. "apple", ">32", "app*"
• Cell references in criteria are not enclosed in quotes, i.e. "<"&A1
• The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).
• To match a literal question mark(?) or asterisk (*), prepend a tilde (~) like (~?, ~*).
• COUNTIF requires a range, you can't substitute an array.
• COUNTIF returns incorrect results when used to match strings longer than 255 characters.
• COUNTIF will return a #VALUE error when referencing another workbook that is not open.

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.