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.

• 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: