COUNT function

Returns the number of values which can be interpreted as numbers. COUNT(1, 2, BLANK(), "3", "test", TRUE, Color.Red)COUNT(1; 2; BLANK(); "3"; "test"; TRUE; Color,Red) returns 4, because only the 1, 2, "3" and TRUE parameters can be interpreted as numbers.

This function only counts numbers, logical values and text values representing numbers (as parsed using TONUMBER). Blank values, in particular, are ignored. To count the number of elements in an array, use SIZE instead.

COUNT takes the language of the app into account when determining if a text string can be interpreted as a number. When COUNT is used with an app whose language is set to German, for instance, text strings representing numbers should use a comma (",") as a decimal separator. When COUNT is used with an app whose language is set to UK English, for instance, a decimal point (".") should be used. Thousands separators may not be used.

Count anything

Using a combination of FILTER and SIZE, you can use any criteria you like. Consider this formula:

FILTER(Field1:Field100, Element > 4)FILTER(Field1:Field100; Element > 4)

The formula above returns a version of the Field1:Field100Field1:Field100 range where only fields whose values are greater than 4 are included. Use the SIZE function to count the number of elements in the returned array:

SIZE(FILTER(Field1:Field100, Element > 4))SIZE(FILTER(Field1:Field100; Element > 4))

The formula above returns the number of fields of the Field1:Field100Field1:Field100 range whose values are greater than 4.

It's easy to replicate what COUNT does armed with this knowledge, and the fact that TYPE can be used to determine if an array element is a number or a logical value:

SIZE(FILTER(Field1:Field100, (TYPE(Element) = 1) || (TYPE(Element) = 4) || !ISERROR(TONUMBER(Element))))SIZE(FILTER(Field1:Field100; (TYPE(Element) = 1) || (TYPE(Element) = 4) || !ISERROR(TONUMBER(Element))))

The formula above returns the number of array elements which are numbers, logical values, or are text strings that can be successfully parsed using TONUMBER and are not blank, which is exactly what COUNT does. TONUMBER returns an error if a text string can't be turned into a number, which ISERROR checks for. || is the "logical or" operator, which is equivalent to the OR function in non-array contexts. ! is the "logical negation" operator which turns FALSE into TRUE and TRUE into FALSE.

Counting can also be done with REDUCE. Here's the equivalent REDUCE formula:

REDUCE(Field1:Field100, (A, V) -> IF((TYPE(V) = 1) || (TYPE(V) = 4) || !ISERROR(TONUMBER(V)), A + 1, A), 0)REDUCE(Field1:Field100; (A; V) -> IF((TYPE(V) = 1) || (TYPE(V) = 4) || !ISERROR(TONUMBER(V)); A + 1; A); 0)

Refer to the REDUCE documentation to learn more about how this example works (specifically, this example is similar to the XOR example in that documentation).

Examples

COUNT(1, 2, BLANK(), "3", "test", TRUE, Color.Red)COUNT(1; 2; BLANK(); "3"; "test"; TRUE; Color,Red)

Returns 4, because only the 1, 2, "3" and TRUE parameters count.

COUNT(Field1:Field100)COUNT(Field1:Field100)

Returns the number of fields of the Field1:Field100Field1:Field100 range whose values can be interpreted as numbers.

SIZE(FILTER(Field1:Field100, I -> (TYPE(I) = 1) || (TYPE(I) = 4) || !ISERROR(TONUMBER(I))))SIZE(FILTER(Field1:Field100; I -> (TYPE(I) = 1) || (TYPE(I) = 4) || !ISERROR(TONUMBER(I))))

Returns the number of fields of the Field1:Field100Field1:Field100 range whose values are numbers or logical values or are text strings which can be parsed with TONUMBER. Here, the Field1:Field100Field1:Field100 range is filtered using FILTER, TYPE, ISERROR and TONUMBER, leaving only values which can be interpreted as numbers. FILTER accepts an array as its first parameter and a condition as its second parameter, and returns an array consisting only of those elements which satisfy the condition. The condition parameter (where Element is set to the array element to be inspected) returns TRUE only if TYPE indicates that the item is either a number or a logical value, or if TONUMBER does not return an error when applied to it. || is the "logical or" operator, which is equivalent to the OR function in non-array contexts. ! is the "logical negation" operator which turns FALSE into TRUE and TRUE into FALSE.

COUNT({ 1, 2, BLANK(), "3", "test", TRUE, Color.Red })COUNT({ 1; 2; BLANK(); "3"; "test"; TRUE; Color,Red })

Returns 4, because only the 1, 2, "3" and TRUE values count. Arrays are supported.

COUNT(1, 2, BLANK(), "3", { "test", TRUE, Color.Red })COUNT(1; 2; BLANK(); "3"; { "test"; TRUE; Color,Red })

Returns 4, because only the 1, 2, "3" and TRUE values count. Arrays mixed with regular numbers are supported.

COUNT("1.0", "2,0", "3,0")COUNT("1.0"; "2,0"; "3,0")

Returns 1 if the app language is set to US English (in which case only "1.0" is classified as a number) and 2 if the app language is set to German (in which case only "2,0" and "3,0" are classified as numbers).