COUNTA function

COUNTA(First, Other...) COUNTA(First; Other...)

First

Number, Logical, Text, Color, { Number }, { Logical }, { Text } or { Color }

The first parameter.

Other

Number, Logical, Text, Color, { Number }, { Logical }, { Text } or { Color } (accepts many)

Additional parameters.

Returns

Number

The number of parameters which are not blank.

Returns the number of values which are not blank. COUNTA(1, 2, BLANK(), "test", FALSE, TRUE)COUNTA(1; 2; BLANK(); "test"; FALSE; TRUE) returns 5, because five of the six values are not blank.

To count the number of values which are numbers, use COUNT. To count the number of elements in an array, use SIZE.

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 COUNTA does armed with this knowledge, and the fact that ISDEFINED can be used to determine if an array element is not blank:

SIZE(FILTER(Field1:Field100, ISDEFINED(Element)))SIZE(FILTER(Field1:Field100; ISDEFINED(Element)))

The formula above returns the number of array elements which are not blank, which is exactly what COUNTA does.

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

REDUCE(Field1:Field100, (A, V) -> IF(ISDEFINED(V), A + 1, A), 0)REDUCE(Field1:Field100; (A; V) -> IF(ISDEFINED(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

COUNTA(1, 2, BLANK(), "test", FALSE, TRUE)COUNTA(1; 2; BLANK(); "test"; FALSE; TRUE)

Returns 5, because five of the six values are not blank.

COUNTA(Field1:Field100)COUNTA(Field1:Field100)

Returns the number of fields of the Field1:Field100Field1:Field100 range whose values are not blank.

SIZE(FILTER(Field1:Field100, ISDEFINED(Element)))SIZE(FILTER(Field1:Field100; ISDEFINED(Element)))

Returns the number of fields of the Field1:Field100Field1:Field100 range whose values are not blank. The Field1:Field100Field1:Field100 range is filtered using FILTER and ISDEFINED, leaving only non-blank values. 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 ISDEFINED returns TRUE when applied to the array element, which it only does if the array element is not blank.

COUNTA({ 1, 2, BLANK(), "test", FALSE, TRUE })COUNTA({ 1; 2; BLANK(); "test"; FALSE; TRUE })

Returns 5, because five of the six values are not blank. Arrays are supported.

COUNTA(1, 2, BLANK(), { "test", FALSE, TRUE })COUNTA(1; 2; BLANK(); { "test"; FALSE; TRUE })

Returns 5, because five of the six values are not blank. Arrays mixed with regular numbers are supported.

Learn

Blog

Pricing