Excel COUNTIF Function
COUNTIF is a function to count cells that meet a single criterion. COUNTIF can be used to count cells with dates, numbers, and text that meet specific criteria. The COUNTIF function 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.
In the example show, the following formulas are used:
Notice COUNTIF is not case-sensitive.
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
The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters. To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
- 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.