×
Inglês
Pedimos desculpa, mas este artigo não está disponível no seu idioma.

COUNTIF function

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

  • COUNTIF(Where do you want to look for something to count?, What do you want to count if Excel finds it?)

  • Example: COUNTIF(List of Cities, City Name)

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Remarks

  •  COUNTIF will not count cells with colors, either applied manually or as a result of Conditional Formatting. To do that, you need to use a User Defined Function in VBA (Visual Basic for Applications). There is a method documented here: Count The Number of Cells with Specific Cell Color by Using VBA.

  •  COUNTIF only works with a single criterion. If you need to count by multiple conditions, you can use the COUNTIFS function.

Example 1 - Count Text Occurrences

COUNTIF function - Count by Salesperson name - Formula in cell E2 is =COUNTIF($A$2:$A$20,"Buchanan")
  • =COUNTIF($A$2:$A$20,"Buchanan")

The formula is looking in the range A2:A20 for each occurrence of the name “Buchanan”. While technically accurate, it is not good practice to hard-code values within formulas. First, it creates a lot of unnecessary typing, which can result in typos and your formula won’t return the results you expect. Second, finding typos can be a chore. Third, formulas like this can be tedious to update. You’re much better using a formula like this, which references a cell value:

  • =COUNTIF($A$2:$A$20,D2)

In this case, we’ve replaced the text “Buchanan” with a reference to the cell value “Buchanan” in cell D2. Both formulas function exactly the same, but the cell reference method is much cleaner and easier to maintain.

Example 2 - Count Values

You’re not just limited to counting text entries with COUNTIF, you can also count numerical values that meet a specific criterion.

COUNTIF function - Count by GPA - Formula in cell F2 is =COUNTIF($B$2:$B$16,3.3)

Notice that the formulas in cells F2:F4 have hard-coded criteria. You'll also see that the first formula is different from the second and third:

  • =COUNTIF($B$2:$B$16,3.3)

This formula doesn't have quotes around the 3.3 value. Only text needs to be enclosed in quotes. However, in the next two examples, the values are in quotes. This is because mathematical operators, in this case greater than (>) and less than (<), do need to be enclosed in quotes. As mentioned in the earlier example, hard-coding values in formulas is not good practice, as it’s error prone and can be tedious to adjust should you need to change values. You’re much better off using direct cell references as seen in the next three formulas in cells G2:G4. The first formula uses a direct cell reference:

  • =COUNTIF(B2:B16,E2)

The second two formulas use mathematical operators, which require a special syntax:

  • =COUNTIF(B2:B16,">"&E3)

The operator (“>”) is enclosed in quotes, but the cell reference isn’t. In this case, you use the Ampersand (&) to tell Excel that you want to join the operator and the cell reference together (this is called Concatenation). So by using this “>”&E3, Excel interprets it as >E3, or >3.3.

Example 3 - Count Dates

Sometimes you need to count the number of dates in a range that meet a certain condition. Like the number of dates that are past January 1.

COUNTIF function - Formula in cell C18 is o	=COUNTIF($B$2:$B$15,">"&B18)

The first and second formulas are:

  • =COUNTIF($B$2:$B$15,">"&B18)

  • =COUNTIF($B$2:$B$15,"<"&B18)

Operators

Here is a list of the mathematical operators you can use with COUNTIF:

Symbol

Meaning

>

Greater Than

<

Less Than

>=

Greater Than or Equal To

<=

Less Than or Equal To

<>

Not Equal To

Wildcards

You can use the asterisk (*) and question mark (?) Wildcard characters to return non-exact matches with COUNTIF. If you want to find an actual (?) or (*), you can add a tilde (~) in front of either.

Formula

Explanation

=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.

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

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

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.

A COUNTIF formula returns 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.

Best Practices

Do this

Why

Use cell references for your Criteria argument

Formulas are easier to maintain and update or change Criteria

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.

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. 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.

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. See more about how to Define and use names in formulas.

See Also

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

Partilhar Facebook Facebook Twitter Twitter E-mail E-mail

As informações foram úteis?

Ótimo! Tem mais feedback?

Como podemos melhorá-lo?

Obrigado pelos seus comentários!

×