In Google Sheets, the non-array functions like **IF**, **SUM**, **SUMIF, MAX**, **AVERAGE**, **VLOOKUP**, etc return only a single value as a result.

But, if you want these functions to result in multiple arrays, Google Sheets has a dedicated function called `ARRAYFORMULA`

for this purpose.

You can nest `ARRAYFORMULA`

with non-array functions. The function only takes one argument which is **array_formula**.

**Syntax:**
**=ARRAYFORMULA(array_formula)**

Here, the argument can be **cell ranges**, a **function returning > 1 output**, or a** mathematical expression of the same size**.

In this article, I will cover all the examples you could use the `ARRAYFORMULA`

.

## ARRAYFORMULA with SORT

During Calculations, you may find the need to sort the numbers in ascending or descending order. Instead of manually sorting numbers and using formulas to calculate, you can perform all of these in one go.

**Example:**

I need to calculate an array of profits as well as sort them in ascending order. To do that, my formula will be:

**=SORT(ARRAYFORMULA(1-B3:B10/A3:A10), 1, TRUE)**

Here, the formula first calculates profit. `ARRAYFORMULA`

returns the array of results—and finally, the SORT function reorders it.

## ARRAYFORMULA and IF

With the Google Sheet’s IF function, you can return a specific item when the Condition is met and not met.

Let’s use the `ARRAYFORMULA`

with IF to get the multiple items for the Condition.

**Example:**

Suppose I need to check whether the Quantity of a Product is greater than 15 or not. Now, if I used this ** =IF(C6:C13 > 15, "Yes", "No") **formula, I’ll get a single value and would have to copy it down for the rest of the columns.

But, If I just converted the formula into an array, it will immediately return the output for all inputs. Isn’t it so much quicker and better to perform calculations?

To do that, here’s my formula

**=ARRAYFORMULA(IF(C6:C13 > 15, "Yes", "No"))**

## ARRAY FORMULA with SUMIF

The SUMIF function is used to add a cell range in certain criteria. But, let’s calculate the conditional sum for multiple values at once.

This method is especially useful when you have the same criteria and the same Array range.

**Example:**

Here, I have a column for Product, Price, and Quantity. I want to add the quantity for all of these products at once.

For that, I entered the formula as

**=ARRAYFORMULA(SUMIF(A6:A, G6:G8, C6:C13))**

In the SUMIF, our criterion is to add the cell ranges that are the same as the Product Column.

## ARRAYFORMULA with VLOOKUP

We all know the VLOOKUP function itself can return only one item from the Lookup table.

But, if you use the `ARRAYFORMULA`

nested, you can return multiple items just like the XLOOKUP.

**Example:**

Using the VLOOKUP table, let’s return the Product ID, Stock, and Price for our Lookup Value. I entered this formula,

**=ARRAYFORMULA(VLOOKUP($G$4, $A$4:$E$8, {2, 4, 5}, FALSE))**

As you can see in the VLOOKUP formula, I have specified to return value from 3 columns which is enclosed inside the column brackets {2, 4, 5}. Then, the `ARRAYFORMULA`

function returns those items.

## ARRAYFORMULA with SUM and IF

Unlike Excel, Google Sheets does not calculate the formulas with SUM and IF nested together.

For example, if you enter` `

**=SUM(IF(A3:B10 < 15, A3:B10,0))**** **formula, you’ll receive a #VALUE! error.

But, if you put the `ARRAYFORMULA`

in the beginning, it’ll return the results.

**=ARRAYFORMULA(SUM(IF(A3:B10 < 15, A3:B10,0)))**

The formula resulted in 68 as output.

## ARRAYFORMULA with UNIQUE, FILTER, COUNTIF

Suppose, I want to return only the unique values from the columns that have duplicates.

For that, I can use the `ARRAYFORMULA`

nested with UNIQUE, FILTER, and COUNTIF to easily extract the required unique values.

**=FILTER(UNIQUE(C6:C13), ARRAYFORMULA(COUNTIF(C6:C13, UNIQUE(C6:C13))>1))**

The formula resulted in 3 unique values from Column I.

## How to Use ARRAYFORMULA To Extract Data From Another Sheet?

`ARRAYFORMULA`

is not only limited to returning an array of results. You can also use it to pull out data from another sheet.

**Example:**

Suppose I need to extract **B3:B10 range from Sheet 1**. For that, my formula would be:

**=ARRAYFORMULA(Sheet1!B3:B10)**

## Things You Should Note When Using ARRAYFORMULA

- There’s a keyboard shortcut to activate the
`ARRAYFORMULA`

. So, you wouldn’t have to enterevery time. After typing the formula, press`=ARRAYFORMULA()`

keys instead of just`Ctrl + Shift + Enter`

**Enter**key. - A Curly Bracket
**{**at the start of the formula also means the formula is an`ARRAYFORMULA`

. - Google Sheets does not allow you to export the
`ARRAYFORMULA`

. - To prevent the
**#SPILL! Behavior**while using this function, make sure there’s space to return the array of results in your spreadsheet. - Use
**ARRAY_CONSTRAIN**Function withto control the unnecessary calculation of array results.`ARRAYFORMULA`

## When Not to Use ARRAYFORMULA?

Some of the Google Sheets functions like** SUMIFS, IFS, and AND** **functions **do not work with the `ARRAYFORMULA`

.

So, if you attempt to use the `ARRAYFORMULA`

with those functions, it’ll result in an error or no changes.