COUNTIF function

apples

32

oranges

54

peaches

75

apples

86

Formula

Description

=COUNTIF(A2:A5,"apples")

Counts the number of cells with apples in cells A2 through A5. The result is 2.

=COUNTIF(A2:A5,A4)

Counts the number of cells with peaches (the value in A4) in cells A2 through A5. The result is 1.

=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)

Counts the number of apples (the value in A2), and oranges (the value in A3) in cells A2 through A5. The result is 3. This formula uses COUNTIF twice to specify multiple criteria, one criteria per expression. You could also use the COUNTIFS function.

=COUNTIF(B2:B5,">55")

Counts the number of cells with a value greater than 55 in cells B2 through B5. The result is 2.

=COUNTIF(B2:B5,"<>"&B4)

Counts the number of cells with a value not equal to 75 in cells B2 through B5. The ampersand (&) merges the comparison operator for not equal to (<>) and the value in B4 to read =COUNTIF(B2:B5,"<>75"). The result is 3.

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,"<=85")

Counts the number of cells with a value greater than (>) or equal to (=) 32 and less than (<) or equal to (=) 85 in cells B2 through B5. The result is 1.

=COUNTIF(A2:A5,"*")

Counts the number of cells containing any text in cells A2 through A5. The asterisk (*) is used as the wildcard character to match any character. The result is 4.

=COUNTIF(A2:A5,"?????es")

Counts the number of cells that have exactly 7 characters, and end with the letters "es" in cells A2 through A5. The question mark (?) is used as the wildcard character to match individual characters. The result is 2.

Wrong value returned for long strings.

The COUNTIF function returns incorrect results when you use it to match strings longer than 255 characters.

To match strings longer than 255 characters, use the CONCATENATE function or the concatenate operator &. For example, =COUNTIF(A2:A5,"long string"&"another long string").

No value returned when you expect a value.

Be sure to enclose the criteria argument in quotes.

A COUNTIF formula receives a #VALUE! error when referring to another worksheet.

This error occurs when the formula that contains the function refers to cells or a range in a closed workbook and the cells are calculated. For this feature to work, the other workbook must be open.

Be aware that COUNTIF ignores upper and lower case in text strings.

Criteria aren't case sensitive. In other words, the string "apples" and the string "APPLES" will match the same cells.

Use wildcard characters.

Wildcard characters —the question mark (?) and asterisk (*)—can be used in criteria. A question mark matches any single character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) in front of the character.

For example, =COUNTIF(A2:A5,"apple?") will count all instances of "apple" with a last letter that could vary.

Make sure your data doesn't contain erroneous characters.

When counting text values, make sure the data doesn't contain leading spaces, trailing spaces, inconsistent use of straight and curly quotation marks, or nonprinting characters. In these cases, COUNTIF might return an unexpected value.

Try using the CLEAN function or the TRIM function.

For convenience, use named ranges

COUNTIF supports named ranges in a formula (such as =COUNTIF(fruit,">=32")-COUNTIF(fruit,">85"). The named range can be in the current worksheet, another worksheet in the same workbook, or from a different workbook. To reference from another workbook, that second workbook also must be open.