An array formula is a formula returning an array of values (of various types) instead of a single number, text string or error value. Any non-array formula that requires at least one non-array argument can be transformed into an array formula by specifying some range/array as that argument. For example:

If there are more such arguments, all of them must represent ranges with the same number of columns and rows. For example:

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

is correct, but

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

will return the #VALUE! error. The dimensions of the returned array match those of the formula arguments.

You can use array formulas whenever you need to generate a series of numbers/labels (as when creating chart data series) or just to simplify some calculations. For example:

to count numbers from the range A1:B100 that are either greater than 100 and smaller than 200 or greater than 250 and smaller than 300, use the following formula:

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

and to sum such values

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

to convert and display errors as empty strings:

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

(Note: If ranges are used, all if() function arguments must represent ranges of the same size.)

to sum values from a range, ignoring errors:

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