For many Google Sheets users, even the fairly seasoned ones, the words ‘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 so much easier, faster, and keeps your worksheets looking so 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.
What are Array Formulas?
Before we explain Array formulas, it’s important to understand what an ‘array’ is. An array is a mathematical concept derived from the concept of Matrices. 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 items of the array and return a single result or a set of results in the form of a new array.
Two Ways to Apply Array Formulas in Google Sheets
In Google Sheets there are two ways in which 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 that 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 makes it different from regular functions is that a regular function returns just a single value while the ARRAYFORMULA function returns a range of cells as output. These functions can also be used 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:
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 indeed seem quite intimidating at first, which often makes you question going the extra mile in trying to learn how to use it.
Well, it certainly is worth the trouble, since array formulas have a number of 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 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 gets automatically applied to any new Array formula rows that are 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:
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:
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 in which array formulas can transform your spreadsheet game. There are a number of other things that you can do with this powerful formula.
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:
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
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 3 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:
Notice that in the second parameter, we specified the range of cells E3:E5. So we can get the results for all the 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 is not working:
To make this work, therefore, 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, by 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:
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 should in column A 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 were even able to use it to check for more than one criteria.
Using Google Sheets Array Formulas 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:
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.
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.