The ARRAYFORMULA applies a single formula to every cell in a defined range and enables the display of values returned into multiple rows and columns.
Syntax=ARRAYFORMULA(array_formula)array_formula – a range, mathematical expression or function returning multiple cells as result.
Sample Usage=ARRAYFORMULA(B2:B7+C2:C7+D2:D7)
//Returns the sum of all the values from the range B2:D7
Objective
Learn how to use the ARRAYFORMULA in Google Sheets and its variations.
Why use the ARRAYFORMULA in Google Sheets?
Let’s say you want to take values from Column 1 and Column 2 and perform some basic operations on them. You can write functions and drag the formula down to apply the function to all the rows. This is a very convenient way to apply the function to all cells, but if the number of rows is less. If the number of rows is 10,000 or 1,00,000, it would take a lot of time to copy and paste the function to all the cells. ARRAYFORMULA in Google Sheets lets you perform this task without manually copy-pasting the function to all the cells.
Advantages of Using the ARRAYFORMULA in Google Sheets
- Since ARRAYFORMULA is a single formula for a huge dataset, it will decrease the amount of formula for the sheet, thereby decreasing the load.
- Also, ARRAYFORMULA is dynamic. So When a new row is introduced into the dataset, the formula will automatically be applied to it.
Example of the ARRAYFORMULA in Google Sheets
- Let’s say you have a dataset consisting of sales of salespeople across different cities.
- You want to calculate the total sales for a salesperson.
- Use the ARRAYFORMULA function, as shown below:
- Click and drag the selection down to the last cell or manually enter the range.
- The output of total sales is shown below:
ARRAYFORMULA with SUM function in Google Sheets
- In the above example, you learn how to add values from different cells of different columns.
- Now, if you want to find total sales by all the salespeople, use the ARRAYFORMULA along with SUM function, as shown below:
=ARRAYFORMULA(SUM(E2:E7))
- The output will be total sales by all salespeople:
ARRAYFORMULA with IF function in Google Sheets
- Let’s say you want to add the sales of Paris and New York and subtract them from total sales.
- This is an indirect way to find the sales in London.
- Here, use ARRAYFORMULA along with the IF function, as shown below:
=ARRAYFORMULA(IF(ISBLANK(E2),,E2:E7-((D2:D7)+(C2:C7))))
- Here, ISBLANK function checks whether the value in a cell is empty or not.
- In the IF function, the first parameter is criteria, second parameter is action if value is false, and third parameter is value if true.
- The first criteria in the IF function is whether the value in the cell is blank or not.
- If the cell is empty, it will return a blank cell.
- If the cell is not empty, it will perform the operation of subtracting Paris and New York sales from Total Sales.
- The output is as shown below:
See Also
Conditional Formatting in Google Sheets: Get started with Conditional Formatting in Google Sheets
Google Sheets: How to Use Pivot Tables: Learning how to create and use Pivot Tables for analysing data in Google Sheets.
Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.
How to use Filter views in Google Sheets | Simple 2-min Guide 💻: Learn how to use Filter view in Google Sheets and its variations.