COUNTIF function

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

Syntax: COUNTIF(range, criteria)

For example:

  • =COUNTIF(A2:A5,"apples")

  • =COUNTIF(A2:A5,A4)

Argument name

Description

range    (required)

The group of cells you want to count. Range can contain numbers, arrays, or references that contain numbers. Blank and text values are ignored.

Learn how to select ranges in a worksheet.

criteria    (required)

A number, expression, cell reference, or text string that determines which cells will be counted.

For example, you can use a number like 32, a comparison like ">32", a cell like B4, or a word like "apples".

COUNTIF uses only a single criteria. Use COUNTIFS if you want to use multiple criteria.

Examples

To use these examples in Excel, drag to select the data in the table, right-click the selection, and pick Copy. In a new worksheet, right-click cell A1 and pick Match Destination Formatting under Paste Options.

Data

Data

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 (using criterion in A4) in cells A2 through A5. The result is 1.

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

Counts the number with oranges (using criterion in A3) and apples (using criterion in A2) in cells A2 through A5. The result is 3.

=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 "<>" (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 3.

=COUNTIF(A2:A5,"*")

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

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

Counts the number of cells that have exactly 7 characters that end with the letters es in cells A2 through A5. The wildcard character ? is used to match individual characters. The result is 2.

Common Problems

Problem

What went wrong

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.

Best practices

Do this

Why

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 ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, COUNTIF might return an unexpected value.

You may need to remove trailing spaces in a cell after table values to get accurate results. Try using the CLEAN function or the TRIM function.

Related

Applies To: Excel 2013, Excel Online



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language