13 Examples with ARRAYFORMULA in Google Sheets

Nowadays, efficiency and effectiveness are appreciated when doing minimal work. As a worksheet in Google Sheets carries a huge amount of data, the calculation to maintain that data should also be efficient. If you add a pile of formulas, there is a huge chance you may end up messing up the calculation. Once you apply a formula, such as ARRAYFORMULA, that works dynamically and keeps expanding automatically, it will eliminate the hassle of adding more formulas.

In this article, we will learn about the examples of ARRAYFORMULA in Google Sheets. You can add ARRAYFORMULA with different functions ( SUMIF, COUNT, IF, FILTER, VLOOKUP, CONCATENATE) or more than one calculation to make the formula dynamic

What is ArrayFormula in Google Sheets?

The ARRAYFORMULA function is an array function that expands the output into different columns and rows dynamically. If you apply ARRAYFORMULA, then you can get multiple outputs rather than getting a single output from any non-array formula function.

Syntax:

=ARRAYFORMULA(array_formula)

(array_formula)- This parameter can be used as a range, multiple ranges, or any functions.

The ARRAYFORMULA function can be added with different types of functions. Here, we will wrap the ARRAYFORMULA function with basic functions such as the basic ARRAYFORMULA function, the CONCATENATE function, and the IF function.

Basic ARRAYFORMULA

The dataset below shows the Price and Quantity of some Fruits. From this dataset, we will get the total costs of each fruit at a time without repeating the same formula every time.

Steps:

➤ Select cell D2 to apply the formula.
➤Apply the ARRAYFORMULA to get the below output.

IF Function with ARRAYFORMULA

Arrayformula can be used with the IF function to get any criteria-based result. There, we will give a 10% discount to all fruit purchased in 5 kg or more. While calculating the discount, we can get all the outputs with just one click.

Steps:

➤ Select cell E2 to apply the formula.
➤ Apply the formula below to get the output at once.

CONCATENATE Function with ARRAYFORMULA

The CONCATENATE function joins text into one cell. Here, we will get the Product Description by applying the CONCATENATE function along with the ARRAYFORMULA Function in cell D2.

Let’s combine the ARRAYFORMULA function with criteria-based functions such as the SUMIF and the COUNTIF functions.

SUMIF Function with ARRAYFORMULA Function

The dataset shows the prices of some fruits in both regions. Let’s apply the SUMIF function, wrapping the ARRAYFORMULA function to get the total cost of both North and South regions.

The following formula is applied in cell B12 to get the total price of each fruit.

COUNTIF with ARRAYFORMULA Function

The COUNTIF function is quite similar to the SUMIF function. Let’s say the following dataset represents the price of some fruits in different regions. We can count the number of areas of each fruit by applying the COUNTIF function, wrapping the ARRAYFORMULA function to get the output at a time.

➤ Apply the following formula in cell B12.

In day-to-day life, we don’t use such a formula where only one condition is applied. There are different conditions in one scenario. So, we use a nested IF function to return the proper output where multiple conditions are applied. Here, getting the Grades as output is quite easy and convenient despite having multiple conditions.

➤ Combine the ARRAYFORMULA function with a nested IF function in cell C2 to return the output as once.

The VLOOKUP function mainly looks up a value from a range and matches that value. Mainly, the simple VLOOKUP function returns a single output, but if you want to make the formula dynamic by adding the ARRAYFORMULA function, then we must add the IF function, with the following both functions addressing the range where the data will be matched. Let’s say we will get the Employee Name from the IDs at once by applying the following formula.

The Filter function filters out unnecessary data and shows only the data that is required. Here, we will apply the FILTER function along with the ARRAYFORMULA function to return the Employee details of the Production department with a single click.

➤ Apply the below formula in cell A14 and other cells with be filled automatically.

➤ The FILTER function can be used to eliminate the blank cells as well. If there is any black cell while applying the FILTER function, then this function automatically eliminates the blank cell.

The below dataset shows the Marks of all the terms for some Students. But the dataset is arranged horizontally. To get the Total Marks of each student, select the data row-wise and sum up the rows as below.

Here, we return total total prices of some fruits where the quantities are different. For instance, the Price of Apple is 34$ and the Quantities are 3,5,7, and kgs. Here we will calculate all the prices regarding the quantities at once by applying the ARRAYFORMULA function.

You can use the ARRAYFORMULA function to join texts as well. Here, we will add columns with the ARRAYFORMULA function to join the texts.

Without Adding Column Name

Let’s assume we have the First Name and the Last Name of the employees. Now we will apply the formula below to add both columns and get the full Employee Name in one column.

Adding Column Name

Sometimes the header is not added or addressed in the dataset. In that case, you can add the header in double quotation marks and wrap the formula in the second bracket as below.

The ARRAYFORMULA function simplifies matrix manipulation. You can complete complex calculations with just a simple click. For instance, the dataset shows two arrays first one is the Stores, and the second one is the Commission. We will multiply these two arrays by applying the ARRAYFORMULA function in cell A11, and the formula will be applied automatically in other cells.

Frequently Asked Questions

Is VLOOKUP an Arrayformula?

Answer: No, the VLOOKUP function is not an arrayformula. This function returns a single value in a single cell. If you want to make this function dynamic, then you need to add the ARRAYFORMULA function and the IF function.

What is an array value in Google Sheets?

Answer: An array value is a range or a combination of values that consists of rows or columns. An array value can never be in a single cell. The output will always consist of multiple cells.

What is an array sample?

Answer: As an array is always a combination of cell ranges or a range, then the array sample is a rectangular object that can be horizontal or vertical.

Wrapping Up

In this article, we learned 13 different examples of arryformula such as combining the ARRAYFORMULA function with basic functions, criteria-based functions, multiple criteria-based functions, the VLOOKUP function, and the FILTER function. We also applied arrayformula horizontally, and combined texts as well. To master arrayformula, keep practicing more and more from this article. If you have any queries about arrayformula, then let us know in the comment section!