Learn Blog Pricing About Sign in

Learn

Blog

Pricing

About

Sign in

COUNTBLANK function

COUNTBLANK(Array) COUNTBLANK(Array)

Array

{ ? }

The array whose element should be counted.

Returns

Number

The number of elements of the given array which are either blank or are equal to the empty text string.

Returns the number of elements of the given array which are either blank or are equal to the empty text string. COUNTBLANK({ 10, BLANK(), "" })COUNTBLANK({ 10; BLANK(); "" }) returns 2, because BLANK()BLANK() and the empty text string "" are considered blank.

Unlike the COUNT and COUNTA functions, this function accepts a single parameter, which must be an array.

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

SIZE(FILTER(TextField1:TextField100, ISBLANK(Element) || Element = ""))SIZE(FILTER(TextField1:TextField100; ISBLANK(Element) || Element = ""))

The formula above returns the number of array elements which are blank or are equal to the empty text string, which is exactly what COUNTBLANK does. This formula uses the || "logical or" operator, which is equivalent to the OR function in non-array contexts.

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

REDUCE(TextField1:TextField100, (A, V) -> IF(ISBLANK(V) || V = "", A + 1, A), 0)REDUCE(TextField1:TextField100; (A; V) -> IF(ISBLANK(V) || 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).

Example

COUNTBLANK({ 10, BLANK(), "" })COUNTBLANK({ 10; BLANK(); "" })

Returns 2, because BLANK()BLANK() and the empty text string "" are considered blank.