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(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.
ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0))) ARRAYFORMULA(A1:C1+A2:C2)
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.
In this formula, the B2:B range designates infinite – this means if a new row is added it will be automatically calculated.