ConditionArray
The array containing the values to test. Only the numbers of the first array, TargetArray , which correspond to the elements of this array which satisfy the corresponding condition and also satisfy the conditions of all the other condition arrays, are used in the final calculation. Two elements are said to correspond to each other if they are in the same position in their respective arrays.
Condition
A text string specifying the condition, a number which array elements must be equal to or a formula fragment specifying the condition. Use a text string like ">1"">1" to specify a condition.
If a text string is given, the following operators are supported: < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), = (equality, case-insensitive for text), == (equality, case-sensitive for text), <> (inequality, case-insensitive for text) and != (inequality, case-sensitive for text). "" means "only blank values," "<>" and "!=" mean "only non-blank values."
If a formula fragment is given, it must return a logical value (TRUE or FALSE) specifying whether the element under consideration should be included. To do its work, it has access to the values Element (the array element currently being tested), Index (the numeric position of the array element currently being tested, starting at 1) and Source , the corresponding condition array.
OtherConditionArray
An additional array containing values to test. The condition is specified as the next parameter.
OtherCondition
A condition for testing elements of the preceding array. Refer to the documentation for the Condition parameter for more information.
Returns
The number of array elements which satisfy one or several conditions.
Returns the number of array elements which satisfy one or several conditions. COUNTIFS ({ 1, 2, 3 }, ">1", { "Eve", "Eve", "Bill" }, "Eve") COUNTIFS ({ 1; 2; 3 }; ">1"; { "Eve"; "Eve"; "Bill" }; "Eve") returns 1, because only the pair 2 and "Eve" satisfies both conditions: 2 is greater than 1 and "Eve" is equal to "Eve". The other two pairs both fail one of the two tests. 1 and "Eve" fails, because 1 is not greater than 1. 3 and "Bill" fails, because "Bill" is not equal to "Eve".
Use & to reference values that reside elsewhere. This formula only counts array elements which are greater than NumberValue :
COUNTIFS works with multiple conditions, but it only requires a single condition. COUNTIF only supports a single condition.
Writing conditions
COUNTIFS supports the following operators: < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), = (equality, case-insensitive for text), == (equality, case-sensitive for text), <> (inequality, case-insensitive for text) and != (inequality, case-sensitive for text).
If a condition parameter is set to "" , only the blank values of the corresponding array are considered. If it is set to "<>" or "!=" , all non-blank values are considered.
If the operator is left out, "=" is assumed. As a result, these formulas are equivalent:
The configured locale is taken into account when parsing numbers that are part of a condition text string, meaning that if the locale is German, a decimal comma (",") should be used as a decimal separator. Similarly, if the locale is Australian English, a decimal point (".") should be used. Thousands separators should not be used and negative numbers should use a leading minus sign ("-").
Specifying the condition using a formula fragment
The condition parameters can also be formula fragments. These formulas are equivalent:
Using a formula fragment gives you access to the full power of the formula locale. Consider this formula:
The formula above returns 1. The only elements which qualify are greater than 1 and are also odd numbers, leaving only a single number, 3.
&& means logical "and," || means logical "or" and ! means logical negation.
This formula returns 3, as 1, 2 and 3 all qualify:
1, 2 and 3 all qualify due to the || operator, which causes all array elements to be counted which are either greater than 1 or are odd.
This formula returns 1, as only 2 is both greater than 1 and is "not odd" (even):
! in ! ISODD (Element) ! ISODD (Element) negates the value returned from ISODD, causing TRUE to become FALSE and vice versa.
The formula fragment is run once for every array element to be tested, and is expected to return TRUE if the element should be considered, and FALSE otherwise. To do its work, it has access to the values Element (the element of the corresponding array currently being tested), Index (the numeric position of the array element currently being tested, starting at 1) and Source , the corresponding array.
If COUNTIFS is used with multiple conditions, either all or none may use formula fragments.
Using wildcards in the condition to match text flexibly
When a condition array contains text strings, the condition can use special characters to match multiple text strings. This formula returns 2, because both "car" and "cat" match the wildcard "ca?":
? matches any character. "ca?" matches not only "car" and "cat", but also "cab", "can" and "cap". However, it does not match "card", because ? matches exactly one character, nor does it match "ca".
* matches no character, a single character, or multiple characters. As such, "ca*" matches everything that "ca?" matches, but also "card", "ca" and "Canada" (matching is case-insensitive).
? and * can be combined in the same pattern. Colo*r ad*s are great? matches both "Color ads are great!" and "Colour adverts are great?".
Sometimes, the special characters ? and * need to be included as-is, and not have special meaning. To achieve that, precede each special character with a tilde ("~"). This formula returns 1, because only "ca*" in the array matches "ca~*":
In other words, to search for a verbatim "*" character, write "~*". To search for a verbatim "?" character, write "~?". Finally, to search for a verbatim "~" character, write "~~".
Regular expressions are much more powerful than wildcards, but are also harder to learn. Use REGEXMATCH in a formula fragment to use regular expressions with COUNTIFS.
This formula returns 1, because only "123" matches the "\d\d\d" regular expression (which only accepts exactly three numbers):
Refer to the documentation of REGEXMATCH for more information.
Examples
Returns 1, because only the pair 2 and "Eve" satisfies both conditions: 2 is greater than 1 and "Eve" is equal to "Eve".
Returns 2, as only 2 and 3 of the array are greater than 1, as specified by the ">1"">1" parameter.
Returns 2, as only the first two array elements are equal to "Eve".
Returns the number of elements of the { 1, 2, 3 }{ 1; 2; 3 } array which are greater than NumberValue .
Returns 1, as only 2 in the array { 1, 2, 3 }{ 1; 2; 3 } satisfies the "=2""=2" condition.
Returns 1, as only 2 in the array { 1, 2, 3 }{ 1; 2; 3 } satisfies the "2""2" condition. Equality is implied when no operator is specified.
Returns 1, as only 2 in the array { 1, 2, 3 }{ 1; 2; 3 } satisfies the 22 condition. When a plain value is given as the second parameter, equality is implied. If only values equal to another value should be counted, using a plain value instead of a value enclosed in a text string is preferable, as errors can be detected at an earlier stage.
Returns 2, as only 1.2 and 1.3 are greater than 1.1, as specified by the ">1.1"">1.1" parameter. A decimal separator appropriate for the configured locale should be used. If that locale is French, for instance, a decimal comma should be used ("1,1" instead of "1.1").
Returns 2, because both "car" and "cat" match the wildcard "ca?". ? matches exactly one (arbitrary) character.
Returns 2, because both "car" and "card" match the wildcard "ca*". * matches zero, one or several (arbitrary) characters.
Returns 1, because only "ca*" matches the wildcard "ca~*". ~ written before any other character matches it verbatim, meaning that * does not have special meaning here.
Returns 2, as only 2 and 3 of the array are greater than 1, as specified by the Element > 1Element > 1 parameter. This example uses a formula fragment instead of a text string. It is invoked once for every array element counted, and is expected to return TRUE if the value should be counted and FALSE otherwise. To do its work, it has access to the Element value, which is the value under consideration.
Returns 1, because only "123" matches the "\d\d\d" regular expression (which only accepts exactly three numbers). Refer to the documentation of REGEXMATCH for more information.