We use various functions in Google Sheets to make our data look meaningful. Most of the time you need to copy-paste the functions or drag them to apply the formulas in rows or columns. If you want to perform this task in an efficient and fast way, there is a formula that makes all this work simple and easy. The **ARRAYFORMULA **function allows you to apply the function to numerous rows and columns at once. In this article, we will discuss how to use this **ARRAYFORMULA **in Google Sheets.

**Table of Contents**hide

## A Sample of Practice Spreadsheet

You can download the practice spreadsheet from the download button below.

## What Is ARRAYFORMULA in Google Sheets?

The **ARRAYFORMULA **function allows applying a single formula on multiple rows or/and columns in an array at the same time.

The **ARRAYFORMULA **is a very time-efficient function. It is dynamic, expandable, and can store data in memory. It is primarily used to present a long array of formula results by utilizing only a single cell.

#### Syntax

The syntax of **ARRAYFORMULA **is:

**ARRAYFORMULA(array_formula)**

#### Argument

ARGUMENT | REQUIREMENT | Function |
---|---|---|

array_formula |
Required | The argument may consist of a cell range, a function, or a math expression for one or more identical-sized arrays. |

#### Output

The formula** =ARRAYFORMULA((C5:C9*D5:D9))** will show the total price of all the items ranged in cell **B5:B9** using the data from cell range **C5:C9** and **D5:D9**.

## 6 Examples to Use ARRAYFORMULA in Google Sheets

Here we will show some examples to demonstrate how to use **ARRAYFORMULA **in Google Sheets.

Let’s assume we have a dataset of some items, with their **quantity **and **unit price**. We will use **ARRAYFORMULA **in this dataset to visualize in a more understandable way.

Follow the article and you will be able to apply **ARRAYFORMULA **by yourself.

### 1. Applying Basic ARRAYFORMULA Function

Here we will use the **ARRAYFORMULA **to find out the total price of the items in the dataset. We previously used the regular multiplication and **fill handle** tool to copy in other cells. On the contrary, now we will use the **ARRAYFORMULA **that will automatically apply the formula to all cells successively.

**📌**** Steps:**

- First, select cell
**E5**and insert the following formula.

`=(C5:C9*D5:D9)`

- However, this will only show the first cell of the range multiplied. Although the other results have been calculated, they are
*not displaye*d. That’s where**ARRAYFORMULA**comes in.

- If you want to get the whole range result also, then you have to copy and paste it into other cells or you can apply the below
**ARRAYFORMULA**in cell**E5**.

`=ARRAYFORMULA(C5:C9*D5:D9)`

- After that, this formula will result in showing all items’ total prices at once.

**Formula Breakdown**

**(C5:C9*D5:D9):**Here, the formula will give only**C5*D5**as output.**=ARRAYFORMULA(C5:C9*D5:D9):**If you put**ARRAYFORMULA**then it will show results from**C5:C9**to**C9*D9**successively.

### 2. Combining IF and ARRAYFORMULA Functions

Now we will use **the IF function** with the **ARRAYFORMULA **function to apply a condition to sort some data. The IF function returns a value depending on whether it is “**TRUE**” or “**FALSE**”.

Suppose, you will only order the items whose unit price is below $1.50. So you want to put the decision in another column.

**📌**** Steps:**

- First, select cell
**E5**and insert the below formula.

`=ARRAYFORMULA(IF(D5:D9<=1.5,`

“Yes”, “No”))

- Then, you will see in the E column the cells will show “
**Yes**” or “**No**” depending on the unit price.

**Formula Breakdown**

**(IF(D5:D9<=1.5, “Yes”, “No”):**If any value from cell**D5**to**D9**is below or equal to 1.5, it will return “**Yes**”, otherwise “**No**”.**D5:D9<=1.5:**The criterion for the IF function. Checks whether the values in the range D5:D9 is less than or equal to 1.5.- “
**Yes**”**:**The result returned if the condition is met. **“No”:**If the value is above 1.5 then the formula will return “No”.

### 3. Utilizing ARRAYFORMULA with SUMIF Function

We can also utilize **ARRAYFORMULA **with **the SUMIF function**. The **SUMIF **function computes a conditional sum over a range. Follow the steps to do it by yourself.

**📌**** Steps:**

- First, select cell
**D12**and enter the formula.

`=ARRAYFORMULA(SUMIF(B5:B9,C12:C13,E5:E9))`

- Then, the formula will automatically show the total price of each item category too.

**Formula Breakdown**

**B5:B9:**This is the**SUMIF function**criteria range.**C12:C13:**Here,**C12**and**C13**are the criteria. The function will find the values from the set criteria.**E5:E9:**It is the range for the sum value. The formula will return the sum of each criterion.

### 4. Applying COUNTIF and ARRAYFORMULA Functions

We often need to find category appearance numbers in a dataset. We use **the COUNTIF function** to do that. It is hectic to copy-paste the formula into a large dataset. We can use the **COUNTIF **and **ARRAYFORMULA **functions together to get values in no time.

**📌**** Steps:**

- First, select cell
**C13**and enter the below formula.

`=ARRAYFORMULA(COUNTIF(B5:B9,{"Apple","Orange"}))`

- Then, This will show the mentioned items’ appearance number in the dataset with only one formula. Otherwise, you would have to make a
**COUNTIF**formula for each category. - In this
**ARRAYFORMULA**, you can see that it shows that “**Apple**” appeared thrice in the dataset and “**Orange**” twice.

- The
**COUNTIF**with**ARRAYFORMULA**shows the output horizontally. If you want to see the output vertically then you need to add**the TRANSPOSE function**in this formula. The**TRANSPOSE**function flips rows and columns. - Insert the following formula to get the vertical output like the following image.

`=ARRAYFORMULA(TRANSPOSE(COUNTIF(B5:B9,{"Apple","Orange"})))`

**Formula Breakdown**

**B5:B9:**This is the**COUNTIF**function criteria range.**{“Apple”,”Orange”}: “Apple”**and**Orange**are the criteria. You can add multiple criteria under**ARRAYFORMULA**.**(TRANSPOSE(COUNTIF(B5:B9,{“Apple”,”Orange”}))):**Here, the**TRANSPOSE**function changes the orientation of data. It transposes rows and columns.

### 5. Utilizing VLOOKUP and ARRAYFORMULA Combination

**The VLOOKUP function** is used to search any data in a table or by row. We can add **ARRAYFORMULA** to put multiple indexes in the **VLOOKUP** formula.

**📌**** Steps:**

- First, select the cell
**B12**and make a dropdown of the**Item Codes**. - After selecting the cell, go to
**Data >> Data Validation**. And a dialog box will appear. - Next, select the range and other options just like the following image.

- You can learn how to make a dropdown from
**here**. - Then, insert the formula in cell
**C12.**

`=ARRAYFORMULA(VLOOKUP($B$12,$B$5:$E$9,{2,3,4},FALSE))`

- Now, if you change the dropdown, the
**VLOOKUP**will automatically search for the data. - Finally, show it in respective columns like the following image.

**Formula Breakdown**

**$B$12:**This**$B$12**is the search key, and the function will search data using this input key. Here add the dollar sign to lock the reference. You can use the**F4**key to do that or read this**article**to learn the method.**$B$5:$E$9:**It is the range for the**VLOOKUP**function.**2,3,4:**These are the column indexes of the table you want to see as search results.**False:**The term “**False**” is given to find the exact match from the data table.

### 6. Using AVERAGE and ARRAYFORMULA Functions

**The AVERAGE function** returns the average of a range of numbers. You can use the **AVERAGE **function within **ARRAYFORMULA **to get the average of any data without making a new column.

**📌**** Steps:**

- First, select the cell
**D11**and enter the formula.

`=AVERAGE(ARRAYFORMULA((C5:C9*D5:D9)))`

- Then, you can see, the
**ARRAYFORMULA**function contains the formula to get the total price. - And you can use the
**AVERAGE**function with that to find the total price average without adding a column.

## Things to Remember

- You can use the
**CTRL+SHIFT+ENTER**shortcut to add the**ARRAYFORMULA**function at the beginning of any formula. - Remember to add the
**dollar ($)**sign in the**VLOOKUP**formula for fixed reference. - You cannot export the
**ARRAYFORMULA**function.

## Conclusion

Hopefully, the methods above will be enough for you to use **ARRAYFORMULA **in Google Sheets. You can check the practice spreadsheet and then practice on your own. Please use the comment section below for any further queries or suggestions. You may also visit our **OfficeWheel** blog to explore more about Google Sheets.