Using the Excel COUNTIF Function


The Excel COUNTIF function accepts a criteria and returns a count of the cells that meets the criteria. For example, the function could be used to count the number of black vehicles that appear in a list.

If you would like to supply more than one set of criteria, use the COUNTIFS function.

The function can work with dates, numbers, text, and various other conditions, including wildcards and logical operators.


=COUNTIF (range, criteria)

Syntax Breakdown

Required. Group of cells you want to count that can include numbers, arrays, named ranges, or cell references. The function ignores blank cells and text values.

Required. Criteria that define which cells will be counted. The argument can be in the form of a number, expression, cell reference, or text. For example, criteria can include 10, "<10", A10, or "10".

If entering text, the function is not case sensitive. For example, "red" is considered the same as "RED".

Non-numeric criteria must be enclosed in double quotation marks ("").

Usage Notes

COUNTIF counts the number of cells that meet a specific criteria. The COUNTIF function only allows the user to specify one criteria, unlike the COUNTIFS function which can accept more than one set of criteria.

When using wildcard characters, the question mark (?) is used to find a single character and the asterisk (*) is used to find a sequence of characters. To find a literal question mark or asterisk, use a tilde (~) in front of the question mark or asterisk.

Common Errors
1) COUNTIF may return incorrect results when used to match strings longer than 255 characters. To avoid this issue, use the CONCATENATE function or the concatenate operator (&).

2) If the function returns the #VALUE! error when referring to another worksheet, ensure that the workbook being referenced is open.

3) If the function returns an unexpected value when counting text values, ensure that the data doesn't contain non-printing characters, leading or trailing spaces, or inconsistent use of quotation marks. The CLEAN or TRIM functions can be used to help avoid this error.