Google Sheets tutorial: Using function ARRAYFORMULA

When exporting email to Google Sheets, the rows will be added continuously. Now, if you want to add a new column that process rows that are dynamically added by cloudHQ you need to use Google Sheets function called ARRAYFORMULA.

Let’s imagine a situation where you’ve got a Google spreadsheet with student names and their subject-wise marks. And you need to get the total for each student.

One way is to compose a formula in the first row and copy-paste it in the other rows.
But that approach will not work because:

    1. The worksheet becomes slow because of individual functions in thousands of cells.
    2. Making any changes in the formula will need hours of editing.
    3. Copy-pasting non-array formulas into their designated cells is a tiresome process.

In this article, we will explain how to use ARRAYFORMULA: formula to dynamically outputs a range of cells instead of just a single value.

What is ARRAYFORMULA?

The ARRAYFORMULA enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays. In other words, ARRAYFORMULA is a function that outputs a range of cells instead of just a single value and can be used with non-array functions.

ARRAYFORMULA Syntax

Syntax

ARRAYFORMULA(array_formula)

array_formula - A range, mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell.

Examples

ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))

ARRAYFORMULA(A1:C1+A2:C2)

ARRAYFORMULA Example

Let’s say that you have a spreadsheet with student names, their for subject-wise marks (2 columns). Now to get the total for subject-wise marks, you can use the ARRAYFORMULA as mentioned below.

=ArrayFormula(B2:B+C2:C)

In this formula, the B2:B range designates infinite – this means if a new row is added it will be automatically calculated.

Tagged: