For many spreadsheet users, even the seasoned ones, the words ‘Google Sheets Array Formula’ can conjure nightmarish images.
That’s why most people tend to steer clear of using array formulas in their worksheets.
However, once you understand its use and application, you will find it makes your work much easier and faster and keeps your worksheets looking much neater.
In this tutorial, we will take you step-by-step through the process and show you, with simple examples, how to use Google Sheets Array formulas.
Table of Contents
What are Array Formulas?
Before we explain how to use an array formula in Google Sheets, it’s essential to understand what an ‘array’ is. An array is a mathematical concept derived from the idea of Matrices (the plural for matrix). Think of an array as a set of values organized in rows and columns, just like cells in a Google Sheets worksheet.
For example, the set of numbers in the range A1 to C4 shown below can be considered as an array with 4 rows and 3 columns:
An Array formula is a formula that is applied to elements of an array like the one shown above. These formulas can perform multiple calculations on the array items and return a single result or a set of results in the form of a new array.
How Do Array Formulas Work In Google Sheets?
The Google Sheets Array formula can be complicated for many sheet users. You can think of the array formula as a row or column of values. What does this mean, though? We can use a simple example to explain. You can put the formula ={11, 2, 3} in a cell. The curly brackets are used for the array formula in Google Sheets to tell the data to be displayed as an array and for the Google Sheets ARRAYFORMULAs that we’ll discuss later..
If you press enter, you get the values in three adjacent columns.
The commas separate the columns, but if you wanted to use rows instead, you would use semicolons (;)
This will give you the values in different rows; one directly below the other one. Hopefully, this was a simple way for you to understand arrays and will contribute to learning how an ARRAYFORMULA in Google Sheets works.
Now let’s say we are trying to multiply some numbers. Normally, the multiply function would take a number from a cell and multiply it with another number from another cell which means you would either select and drag the function or use a repeat function.
If we were to use the Google Sheets ARRAYFORMULA function instead, we would be multiplying a data range with another data range. An array formula lets you use multiple formulas at once by allowing functions that don’t work with arrays to do so. This is one of the ways to apply a formula to an entire column. Like in the example below:
Two Ways to Apply Array Formulas in Google Sheets
In Google Sheets, there are two ways you can apply array formulas to your data. The first method is by clicking in the formula bar while your cursor is on the formula you want to convert and then pressing the CTRL+Shift+Enter shortcut (Cmd + Shift + Enter on a Mac) on your keyboard.
The second way is by using the ARRAYFORMULA function.
The ARRAYFORMULA Function
The ARRAYFORMULA function performs operations on a range of cells and outputs a range of cells instead of just a single value. What differentiates it from regular functions is that a standard function returns just a single value, while the ARRAYFORMULA function returns a range of cells as output. You can also use these functions with non-array functions to make them return an array (or a range of values).
Syntax for the ARRAY FORMULA Function
The syntax for the ARRAYFORMULA function is as follows:
=ARRAYFORMULA(array_formula)
As you can see, the ARRAYFORMULA function accepts just one parameter, which can be one of the following:
- a reference to a range of cells,
- a mathematical expression using cell ranges of the same size, or
- a function that returns a range of cell values.
Why Use Array Formulas?
The obvious question that comes to mind is ‘why bother’? Array formulas can seem quite intimidating at first, which often makes you question going the extra mile to learn how to use them. The ARRAYFORMULA Google spreadsheet can be used to do complex calculations like calculating running totals.
Well, it certainly is worth the trouble since array formulas have several benefits. Here are just a few of them:
- They let you perform multiple calculations at once
- They let you repeat one or more calculations many times on a selected range of cells
- Array formulas are quite helpful when you want to apply a single formula to a whole column or range of cells. Unlike a regular formula, which needs to be pasted down to the entire column, an array formula lets you skip the step of pasting down, by displaying results for the entire column in one go.
- Usually, when a computation involves some intermediate results, you need to use some ‘helper’ columns to store them. However, using array formulas lets you skip these intermediate steps and directly display the final result. This gets your work done quickly and keeps your worksheet looking neater.
- The single array formula is expandable. So if you make a change to one place, it expands down to the entire range of cells.
- Array formulas are dynamic. The formula is automatically applied to any new Array formula rows introduced into the data range.
How are Array Formulas Used in Google Sheets?
Let us see a simple example to understand how to use array formulas. Say you have the following data with the unit cost and quantity sold for each item:
To find the total sales amount for each item, you would normally need to multiply the unit cost and quantity sold for the first item by applying the formula =B2*C2 and then use a fill handle to paste the formula down to the rest of the cells of column D. This means for 5 items, you have 5 separate formulae.
Using array formulas, however, you can skip the pasting-down step and directly get the total sales for each item using a single formula, as follows:
=ARRAYFORMULA(B2:B5*C2:C5)
As soon as you press the return key after entering the above formula in cell D2, you get the following result:
The array formula takes each cell from the range B2:B5 and multiplies with the corresponding cell from the range C2:C5, displaying the result in the corresponding cell of column D. As you can see, with just one formula, we got total sales for all 5 items!
If your main target is to obtain the Grand total of sales (for all items), you probably don’t want to display the extra column for total sales per item (shown in column D in the above screenshot). Array formulas make it possible for you to achieve this with a single formula, and without the extra helper column D. Here’s the formula that you can use to get the Grand Total:
=SUM(ARRAYFORMULA(B2:B5*C2:C5))
That’s all! You did not need to introduce the extra column D, and all your work got done with a single formula!
Simple Example Using Google Sheets Array Formulas
Quickly finding grand totals and avoiding pasting down formulas are just two of the ways array formulas can transform your spreadsheet game. There are a number of other things that you can do with this powerful formula. It’s easier to learn how to use the array formula in Google Sheets with ARRAYFORMULA examples
Here are a few more ways in which Array formulas can be used:
Using Array Formulas for Matrix Multiplication
Array formulas provide a great way to perform matrix multiplications. For example, Say you have a row matrix named Matrix1 consisting of 5 rows and a column matrix named Matrix2 consisting of 4 columns, as shown below:
You can find the product of these two matrices using the array formula:
=ARRAYFORMULA(D2:G2*B3:B7)
As you can see from the screenshot below, this gives a 5×4 matrix containing the product of Matrix1 and Matrix2:
Note: This could also be done using the MMULT function in Google Sheets.
Using Array Formulas with Aggregation Functions like COUNTIFS and SUMIFS
You can also use Array formulas with aggregation functions like SUMIF, SUMIFS, COUNTIF and COUNTIFSÂ to quickly sum up values in a range based on one or more conditions.
For example, say you have the following sales amounts item-wise for each store outlet:
You want to find out the total sales for each item and display these results in cells F3:F5. If you used a simple SUMIF function for this, you would need to apply the formula three times (one time for each item).
This is because the SUMIF function only allows you to specify one cell in the condition parameter at a time. However, with array formulas, it is possible to specify a range of cells in the condition parameter as follows:
=ARRAYFORMULA(SUMIF(B2:B10,E3:E5,C2:C10))
Notice that in the second parameter, we specified the range of cells E3:E5. So we can get the results for all three items at once!
You could use the COUNTIFÂ function with the Array formula in the same way, if you need to count the number of entries for each item.
Applying the array formula with the SUMIFS and COUNTIFS functions is a little more complex. Since SUMIFS and COUNTIFS basically return the sum of an array based on multiple conditions, the result can only be a single value.
As such, even if you use an array formula with SUMIFS or COUNTIFS functions, it will not make a difference. You will only get one result, as shown in the example below:
As we can see from the screenshot shown below, even using Google Sheets Array formulas isn’t working:
To make this work, you will need to improvise your formula by using the SUMIF function. Now an obvious question comes to mind – How can you use multiple conditions with a SUMIF function? The answer is using the ampersand operator (&) and an array formula!
So say you wanted to find the total sales for each item only from the UK outlet.
Your formula would then be:
=ARRAYFORMULA(SUMIF(A2:A10&B2:B10,F3:F5&E3:E5,C2:C10))
Note that we have two conditions here:
- The item name in column B should match the item name of the corresponding cell in column E
- The Outlet in column A should match the outlet in column F
We simply combined the ranges and their corresponding criteria using the ampersand operator. After this, we wrapped an ARRAYFORMULA function around the SUMIF formula.
The SUMIF function now expands its results to the entire column, and we could even use it to check for more than one criterion.
How to Use the Google Sheets Array Formula With the IF Function
The IF function is usually used to set conditions to a formula to return two outcomes: either FALSE or TRUE. You can also use the Google Sheets array function with the IF function. Let’s use an example to understand further.
In the table below, we have the number of shoe sales for different brands. Let’s say you want to find out which shoe brand has sold more than ten shoes.
You first select the column where you’re results will appear, then type the IF formula in the formula bar
=IF(C2:C11>10,”Yes”,”No”)
Use the keyboard shortcut Ctrl + Shift+ Enter to input the ARRAYFORMULA. (Use Command + Shift+ Enter for Macbook)
Once you press Enter, you will get the results in the selected column. Shoes that sold more than 10 pairs will be indicated as Yes and those with fewer pairs as No
Example WorkSheet
Follow the link if you want to use our example Google worksheet.
How to Use VLOOKUP and ARRAYFORMULA in Google Sheets
We can use the array formula in Google Sheets together with the VLOOKUP to look up data in more than one column and retrieve the data and its corresponding information.
For example, let’s say we want to find out how many shoes and how many sales we made from shoe stock number 67 in our worksheet below.
If you were to use the VLOOKUP formula on its own, it would only retrieve numbers sold for the stock number.
=VLOOKUP(“4006-674-67”,$A$2:$E$11,4,0)
If we combine it with the Google Sheet ARRAYFORMULA function, however, we can get multiple values for multiple columns or rows at once. This way, you can look up more data with just one formula rather than doing the VLOOKUP formula one by one.
If we wanted to get multiple values for one stock number, for example, the item, brand, total sales, and sold>10 for the first stock number, we would use the following formula:
=ARRAYFORMULA(VLOOKUP($A$2,$A$1:$G$9,{2,3,6,7},FALSE))
The numbers in the curly brackets represent the columns from which you want to retrieve the data. This will return the values from the columns into a new row, as shown below:
There are multiple ways to use array formulas in Google Sheets with the VLOOKUP formula, but for this example, we used a simple method for easy understanding.
You can also use VLOOKUP with the ARRAYFORMULA function with multiple criteria or create new sheets with some of the data from the old sheets. Then, you can also use these two formulas together to join two sheets.
How to Remove Extra Blank Cells in Google Sheets ARRAYFORMULA
For most functions, you use together with the array formula function, if you have blank cells, you will get the error #REF!
If we were using the SUMIF function with the ARRAYFORMULA in Google Sheets and there were some extra empty cells, then the formula would return 0 array values for the empty cells.
To remove the extra cells, you can use the function IF LEN function by using a formula similar to this ARRAYFORMULA:
=ARRAYFORMULA(IF(LEN(I2:I11),(SUMIF(B2:B11,I2:I11,D2:D11)),))
This formula will eliminate all the extra blank cells.
The array formula in sheets will also return 0 for extra blank cells in the criteria region. In this case, to remove the extra blank cells, we can use IF+ISBLANK. This works in the same way as IF+LEN above.
If you are using the ARRAYFORMULA in Google Sheets together with VLOOKUP it will usually return N/A if there are blank cells. To ignore the blank cells we can add the IFERROR formula
In the formula:Â
=ARRAYFORMULA(VLOOKUP(I2:I11,B2:D11,3,0))Â
Add the IFERROR function before the array formula. This will eliminate the N/A and leave the cells blank.
Using Google Sheets Array Formula to Concatenate Text
We can also use array formulas to combine columns and concatenate the text contained in them. For example, we have in the screenshot below, a list of first and last names.
If you want to combine these two columns in one go, you can simply use the array formula with the concatenation operator as follows:
=ARRAYFORMULA(A2:A7&” “&B2:B7)
When you press the return key after typing in the above formula, you will find all the names and surnames combined together with a space in between.
In the same way, you can use the concatenation operator to quickly combine any type of text with numbers, dates, or special characters.
Conclusion
In this tutorial, we provided you with an easy guide to Google Sheets Array formulas. We explained how they work, and with suitable examples, we showed you how versatile these formulas can be. We hope you found our step-by-step guide helpful and easy to follow.
