COUNTIF Function

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. 

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:

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.

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

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 does not count long numbers greater than 15 digits correctly. Example here.
• COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.
• If you reference a range in an external workbook, COUNTIF requires that the workbook be open to calculate a result. If the external workbook is not open, you will see a #VALUE! error. As a workaround, you can switch to the SUMPRODUCT function, which does not have this limitation. The syntax will look like this: =SUMPRODUCT(--(criteria_range=criteria)). See this page for details.
• COUNTIF has other quirks explained in this article.

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.