Excel COUNTIF Function
- range - The range of cells to count.
- criteria - The criteria that controls which cells should be counted.
The COUNTIF function in Excel counts the number of cells in a range that match one supplied condition. Criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Criteria can also be based on a value from another cell, as explained below.
COUNTIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIF requires a cell range, you can't use an array.
COUNTIF only supports a single condition. If you need to apply multiple criteria, use the COUNTIFS function. If you need to manipulate values in the range argument as part of a logical test, see the SUMPRODUCT and/or FILTER functions.
In the worksheet shown above, the following formulas are used in cells G5, G6, and G7:
Notice COUNTIF is not case-sensitive, "CA" and "ca" are treated the same.
Double quotes ("") in criteria
In general, text values need to be enclosed in double quotes (""), and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes, as seen in the second example below:
Value from another cell
A value from another cell can be included in criteria using concatenation. In the example below, COUNTIF will return the count of values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.
=COUNTIF(A1:A10,"<"&B1) // count cells less than B1
Not equal to
To construct "not equal to" criteria, use the "<>" operator surrounded by double quotes (""). For example, the formula below will count cells not equal to "red" in the range A1:A10:
=COUNTIF(A1:A10,"<>red") // not "red"
COUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:
The easiest way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than the date in B1, you can use a formula like this:
=COUNTIF(A1:A10, ">"&B1) // count dates greater than A1
Notice we must concatenate an operator to the date in B1. To use more advanced date criteria (i.e. all dates in a given month, or all dates between two dates) you'll want to switch to the COUNTIFS function, which can handle multiple criteria.
The safest way hardcode a date into COUNTIF is to use the DATE function. This ensures Excel will understand the date. To count cells in A1:A10 that contain a date less than April 1, 2020, you can use a formula like this
The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:
=COUNTIF(A1:A5,"*apple*") // cells that contain "apple"
To count cells in A1:A5 that contain any 3 text characters, you can use:
=COUNTIF(A1:A5,"???") // cells that contain any 3 characters
The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).
- COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.
- COUNTIF only supports one condition. Use the COUNTIFS function for multiple criteria.
- Text strings in criteria must be enclosed in double quotes (""), i.e. "apple", ">32", "ja*"
- Cell references in criteria are not enclosed in quotes, i.e. "<"&A1
- The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).
- To match a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
- COUNTIF requires a range, you can't substitute an array.
- COUNTIF returns incorrect results when used to match strings longer than 255 characters.
- COUNTIF will return a #VALUE error when referencing another workbook that is closed.