Count values in a range that meet a single criteria.

COUNTIF(range,criteria)

---

COUNTIF (without the 's'!) allows you to count values associated with one set of specific criteria in a range or table.

To count values associated with multiple criteria, use the COUNTIFS function.

The COUNTIF syntax requires the range to be searched and the criteria to look for.

COUNTIF can be used with dates, numbers, and text and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

---

COUNTIF examples.

In the examples below, the data range shows a list of countries along with some population and Gross Domestic Product (GDP) per capita statistics. COUNTIF is used to count how many countries meet set numerical, logical or text criteria.

Note: COUNTIF works slightly differently to the COUNTIFS (with the 's'!) function which allows you to count values associated with multiple criteria. For example, with a COUNTIFS function, you could count the number of countries in a specific region that also matched specific population criteria. This would not be possible with the COUNTIF function which only permits one criterion.

---

Syntax

The COUNTIF function consists of 2 arguments: =COUNTIF(range,criteria)

1. range. Required. The range of cells to count.
2. criteria. Required. The criteria used to evaluate which cells should be counted.

Try it now!

• Enter your formulas into the grey cells.

---

Scope

COUNTIF can be applied to ranges, named ranges and tables. It can also be used with logical conditions, wildcards and cell references.

Range:	=COUNTIF(C3:C13,"South Asia")
Named Range:	=COUNTIF(Region,"South Asia")
Table:	=COUNTIF(Table1[Region],"South Asia")
Logical Condition:	=COUNTIF(C3:C13,"<>South Asia") // not equal to "South Asia"
Wildcard *:	=COUNTIF(C3:C13,"*Asia") // ends with "Asia"
Wildcard ?:	=COUNTIF(C3:C13,"?????") // contains 5 characters
Cell reference:	=COUNTIF(C3:C13,"<>"&A2) // not equal to value of cell A2

Caveats

COUNTIF function:

• is not case sensitive.
• logical operators (<,>,<>,=) can be used for partial matches and should be enclosed in double quotation marks "" along with non-numeric criteria being evaluated (quotation marks are not required for numeric values).
• cell references in criteria are not enclosed in quotes, i.e. "<"&A1
• can count blank cells =COUNTIF(C3:C7,"<>") and non-blank cells =COUNTIF(C3:C7,"").
• use the * and ? characters for wildcards. The asterisk * matches any sequence of characters. The question mark ? matches any single character. To find an actual question mark or asterisk, type a tilde (~) preceding the character.
• returns incorrect results when used to match strings longer than 255 characters.
• returns a #VALUE! error when referencing another workbook that is closed.

---

Common Issues

• Wrong values returned: COUNTIF has a 255 character limit for text strings. To match text strings longer than this 255 character limit, use a concatenate (&) operator. For example =COUNTIF(C3:C7,"first long text string"&"second long text string").
• Use the TRIM and CLEAN functions to remove leading spaces, trailing spaces or non-printing characters and correct inconsistent use of curly and straight quotations.
• No value returned: Double check that any logical operator or text values are enclosed in quotations.
• #VALUE!: Check the COUNTIF function isn't referring to a separate workbook that is closed.

---

Related Functions

• COUNTIFS count values within a range that meet specific or multiple criteria.
• SUMIF: sum (add) values in a range that meet a single criterion.
• SUMIFS: sum (add) values in a range that meet specific or multiple criteria.

Functions Category

• Statistical