Table of Contents

**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.

**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.
- Here, array_formula can be a cell range, mathematical expression or a function returning multiple cells as result.

**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.

## One response to “All You Need to Know About ARRAYFORMULA in Google Sheets”

[…] ARRAYFORMULA in Google Sheets: Learn how to use the ARRAYFORMULA function in Google Sheets and its variations. […]