Excel COUNTIFS Function
The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
- range1 - The first range to evaulate.
- criteria1 - The criteria to use on range1.
- range2 - [optional] The second range to evaluate.
- criteria2 - [optional] The criteria to use on range2.
The COUNTIFS function counts cells that meet one or more criteria. Unlike the older COUNTIF function, COUNTIFS can apply more than one condition at the same time. Conditions are supplied with range/criteria pairs, and only the first pair is required. For each additional condition, supply another range/criteria pair. Up to 127 range/criteria pairs are allowed.
COUNTIFS 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 COUNTIFS requires a cell range for range arguments, you can't use an array.
With the example shown, COUNTIFS can be used to count records using 2 criteria as follows:
Notice the COUNTIFS function 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 shown below:
Note: showing one condition only for simplicity. Additional conditions must follow the same rules.
Value from another cell
When using a value from another cell in a condition, the cell reference must be concatenated to an operator when used. In the example below, COUNTIFS will count the 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, but the cell reference is not:
=COUNTIFS(A1:A10,"<"&B1) // count cells less than B1
Note: COUNTIFS is one of several functions that split conditions into two parts: range + criteria. This causes some inconsistencies with respect to other formulas and functions.
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:
=COUNTIFS(A1:A10,"<>red") // not "red"
COUNTIFS 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 COUNTIFS 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 a date in B1, you can use a formula like this:
=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1
Notice we concatenate the ">" operator to the date in B1, but and are no quotes around the cell reference.
The safest way to hardcode a date into COUNTIFS is with the DATE function. This guarantees Excel will understand the date. To count cells in A1:A10 that contain a date less than September 1, 2020, you can use:
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 A1:A5 that contain the text "apple" anywhere, you can use a formula like this:
=COUNTIFS(A1:A5,"*apple*") // count cells that contain "apple"
The tilde (~) is an escape character to allow you to find literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).
The COUNTIFS function is designed to apply multiple criteria, but conditions are applied with AND logic. This means if you try to count cells that contain "red" or "blue" in the same range, the result will be zero (0). However, to count cells with OR logic, you can use an array constant and the SUM function like this:
The formula above will count cells in range that contain "red" or "blue". Briefly, COUNTIFS returns two counts in an array (one for "red" and one for "blue") and the SUM function returns the sum as a final result. For more information, see this example.
- Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.
- Each additional range must have the same number of rows and columns as range1, but ranges do not need to be adjacent. If you supply ranges that don't match, you'll get a #VALUE error.
- Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a number).
- 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. ~?, ~*).