COUNTIF function
ConditionArray
The array containing the values to test.
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 array.
Returns
The number of array elements which satisfy a condition.
Returns the number of array elements which satisfy a condition. COUNTIF({ 1, 2, 3 }, ">1")COUNTIF({ 1; 2; 3 }; ">1") returns 2, as only 2 and 3 of the array are greater than 1, as specified by the ">1"">1" parameter. COUNTIF({ "Eve", "Eva", "Ev", "Bill" }, "Ev*")COUNTIF({ "Eve"; "Eva"; "Ev"; "Bill" }; "Ev*") returns 3, as only the first three elements of the array start with "Ev", given as the second parameter.
Use & to reference values that reside elsewhere. This formula only counts array elements greater than the value of Field1:
Use COUNTIFS to use multiple conditions (or use COUNTIF with a formula fragment and &&, described below).
Writing conditions
COUNTIF 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 the second parameter is set to "", only the blank values of
the array are counted. If it is set to "<>" or
"!=", all non-blank values are counted.
If the operator is left out, "=" is assumed. As a result, these
formulas are equivalent:
The language settings of the app are taken into account when parsing numbers that are part of the condition text string, meaning that an app configured to use German should use a decimal comma (",") as a decimal separator and an app configured to use Australian English should use a decimal point ("."). Thousands separators should not be used and negative numbers should use a leading minus sign ("-").
Specifying the condition using a formula fragment
The second parameter can also be a formula fragment. These formulas are equivalent, with the second one using a formula fragment:
Using a formula fragment gives you access to the full power of the formula language. Consider this formula:
The formula above returns 1, as 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 counted, and FALSE
otherwise. To do its work, it has access to the values Element
(the element of the array currently being tested), Index (the
numeric position of the array element currently being tested, starting at 1)
and Source, the array.
Using wildcards in the condition to match text flexibly
When the 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 COUNTIF.
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.
Using FILTER, SIZE and REDUCE instead of COUNTIF
The work COUNTIF performs can be divided into two parts: filtering out the unwanted elements from the array and then counting the number of remaining elements. As such, these formulas are equivalent:
REDUCE can also be used to count elements in an array that satisfy a condition. These formulas are equivalent:
Refer to the documentation for REDUCE for more information on how the REDUCE formula works.
Examples
Returns 2, as only 2 and 3 of the array are greater than 1, as specified by the ">1"">1" parameter.
Returns the number of fields of the Field1:Field5Field1:Field5 range whose values are greater than 1.
Returns the number of employees who earn more than $50,000 per year. Salary is a named value, belonging to the Employees screen, whose formula is an array specifying the contents of this table column. The named value and its formula can be created and maintained using a spreadsheet-like editor in Calcapp Creator.
Returns 2, as only the first two array elements are equal to "Eve".
Returns 3, as only the first three array elements match the wildcard "Ev*". "Ev*" matches all text strings starting with "Ev".
Returns the number of elements of the { 1, 2, 3 }{ 1; 2; 3 } array which are greater than the value of Field1.
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 language of the app should be used. If that language 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, as only 3 in the array is both greater than 1 and is an odd number. Formula fragments have access to the full power of the formula language, enabling complex conditions. && means logical "and," || means logical "or" and ! means logical negation.
Returns 2, as only 2 and 3 in the array are greater than 1. FILTER returns a version of the array where the elements failing the Element > 1Element > 1 test are not present. Finally, SIZE returns the number of elements of the filtered array.
Returns 2, as only 2 and 3 of the array are greater than 1. Refer to the documentation of REDUCE to learn how it works.
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.