Advertisements

COUNTIF function

Applies to: Excel for Office 365, Excel for Office 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

With COUNTIF, one of the statistical functions, you can count the amount of cells which qualify a criterion; for instance, to count how often a specific city recurs in a customer list.

In its lowest form, COUNTIF says:

  • =COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

  • =COUNTIF(A2:A5,”London”)
  • =COUNTIF(A2:A5,A4)

Syntax

COUNTIF(range, criteria)

Argument nameDescription
range    (required)The set of cells you want to count. Range can include numbers, arrays, a named range, or references that encompass numbers. Blank and text values are skipped. Learn how to select ranges in a worksheet.
criteria    (required)A number, expression, cell reference, or text string that dictates which cells will be counted.For example, with a number like 91, you can apply a comparison such as “>91”, a cell like A1, or a word like “kiwi”. COUNTIF uses merely a linear criteria. Use COUNTIFS when you are dealing with using multiple criteria.

Examples

To practise with such examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

DataData
apples32
oranges54
peaches75
apples86
FormulaDescription
=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. As a result, you get 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. As a result, you get 3. This formula uses COUNTIF twice to specify multiple criteria, one criteria per expression. Also, you could use the COUNTIFS function.
=COUNTIF(B2:B5,”>55″)Counts the number of cells with a value greater than 55 in cells B2 through B5. As a result, you get 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″). As a result, you get 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. As a result, you get 3.
=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. Effectively, you get 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.

Common Problems

ProblemWhat went wrong
Wrong value returned for long strings.The COUNTIF function yields incorrect results when you use it to match strings exceeding 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.Above all, you must surround the criteria argument in quotes.
A COUNTIF formula receives a #VALUE! error when referring to another worksheet.This error happens when the formula that has the function relates to cells or a range in a closed workbook and the cells are calculated. To guarantee flawless operation, the other workbook should be open.

Best practices

Do thisWhy
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 fits any single character. An asterisk equals any sequence of characters. However, if you want to pinpoint an actual question mark or asterisk, enter a tilde (~) ahead of the character. For example, =COUNTIF(A2:A5,”apple?”) will count all results of “apple” with a last letter that could differ.
Make sure your data doesn’t contain erroneous characters.When counting text values, check the data doesn’t include leading spaces, trailing spaces, irregular use of straight and curly quotation marks, or nonprinting characters. Specifically, in such cases, COUNTIF could generate an unexpected value. Try using the CLEAN function or the TRIM function.
For convenience, use named rangesCOUNTIF supports named ranges in a formula (such as =COUNTIF(fruit,”>=32″)-COUNTIF(fruit,”>85″). The named range can be in the existing worksheet, another worksheet in the same workbook, or from another workbook. To reference from another workbook, that second workbook also has to be open.

Note: The COUNTIF function will not count cells based on cell background or font colour. However, Excel supports User-Defined Functions (UDFs) using the Microsoft Visual Basic for Applications (VBA) operations on cells based on background or font colour. Here is an example of how you can Count the number of cells with specific cell colour by using VBA.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See also

To count cells that aren’t blank, use the COUNTA function

To count cells using multiple criteria, use the COUNTIFS function

See a video on how to use the COUNTIFS function

The SUMIF function adds only the values that meet a single criteria

The SUMIFS function adds only the values that meet multiple criteria

IFS function (Office 365, Excel 2016 and later)

Use the TRIM function to remove leading and trailing spaces from cells

Use the CLEAN function to remove non-printing characters from cells

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Statistical functions

Excel functions (alphabetical)

Excel functions (by Category)

Using SUMIF, COUNTIF, and related functions for quick data analysis (free preview)

Advertisements

Leave a Reply

%d bloggers like this: