# Excel COUNTIF Function

COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

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

### Basic example

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"

### Blank cells

COUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

### Dates

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

### Wildcards

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. ~?, ~*, ~~).

### Notes

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

## Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.