1. Overview

An array formula returns an array of values — numbers, text, Booleans, or errors — instead of a single value. Any non‑array formula becomes an array formula when at least one of its arguments is replaced with a range or an explicit array.

Examples:

When a formula argument is expanded into a range or array, the formula evaluates element‑by‑element and returns an array whose dimensions match those of the array arguments.

2. Matching Dimensions

If a formula contains multiple array or range arguments, all of them must have identical dimensions — the same number of rows and columns.

Correct:

=sqrt(E5:F6) + {1, 2; 3, 4}

Incorrect (returns #VALUE!):

=sqrt(E5:F6) + {1, 2}

The returned array always matches the dimensions of the array arguments. If any argument has mismatched dimensions, the entire formula produces a #VALUE! error.

3. Typical Uses

Array formulas are useful for generating sequences (e.g., chart data), performing element‑wise operations, or simplifying complex logical and arithmetic expressions.

Counting values in ranges

Count numbers in A1:B100 that are either:

• greater than 100 and smaller than 200, or
• greater than 250 and smaller than 300

=sum(((A1:B100 > 100)*(A1:B100 < 200) + (A1:B100 > 250)*(A1:B100 < 300)))

This works because logical comparisons return arrays of 0s and 1s, and multiplication acts as a logical AND while addition acts as a logical OR.

Summing values that meet conditions

=sum(((A1:B100 > 100)*(A1:B100 < 200) + (A1:B100 > 250)*(A1:B100 < 300)) * A1:B100)

The conditional mask is multiplied element‑wise by the numeric range, producing an array of values that meet the conditions.

Converting errors to empty strings

=if(isError(b1:c5), if(errorType(b1:c5)=0, "", ""), b1:c5)

Note: When ranges are used, all if() arguments must have identical dimensions.

Summing values while ignoring errors

=sum(if(isError(b1:c5), errorType(b1:c5)=0, b1:c5))

Here, errorType() is used to distinguish real errors from non‑errors (errorType = 0). Only valid numeric values are included in the final sum.