All You Need to Know About ARRAYFORMULA in Google Sheets

Reading Time: 4 minutes

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.
Sales data
Figure 1: Sales data
  • You want to calculate the total sales for a salesperson.
  • Use the ARRAYFORMULA function, as shown below:
Example of ARRAYFORMULA in Google Sheets
Figure 2: Example of ARRAYFORMULA function
  • Click and drag the selection down to the last cell or manually enter the range.
  • The output of total sales is shown below:
Figure 3: Total sales data using ARRAYFORMULA

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))

Example of ARRAYFORMULA with the SUM function
Figure 4: Example of ARRAYFORMULA with the SUM function
  • The output will be total sales by all salespeople:

Figure 5: Output is sum of total sales
Figure 5: Output is sum of total sales

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))))

Example of ARRAYFORMULA with IF function
Figure 6: Example of ARRAYFORMULA with IF function
  • 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:
Output is only London Sales
Figure 7: Output is only London Sales

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.

shalinamigo

←
→

Leave a Reply