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:
-
- The worksheet becomes slow because of individual functions in thousands of cells.
- Making any changes in the formula will need hours of editing.
- 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.