How Do Array Formulas Work in Google Sheets? | Array Formula with Examples

Google Sheets Array Formula: An array, as we all know, is a collection of similar kinds of data in the form of rows and columns that helps in storing, arranging, and manipulating records. In Google Sheets too, we will need to encounter arrays along with various calculations based on their records, which might be tricky as well as time-consuming if you do not know the actual formulas.

This article will walk you through the basic formulas you will need to know when working with arrays in Google Sheets. Stay tuned till the end to unleash more of these important Google Sheet Tips.

What Is the Array Formula in Google Sheets?

An array formula basically allows us to retrieve a range of cells as an output rather than a single value. We can also use non-array functions with arrays, like a range of data. To use array functions in Google Sheets, either type ARRAYFORMULA directly or press Ctrl+ Shift+ Enter (Windows) or Cmd+ Shift+ Enter (Mac) while keeping your cursor pointed to the formula bar.

This is one single formula that works for a huge data set. It is expandable which expands down the entire database if there is some change in a single place. Also, the Array Formula is dynamic. This means that if a new row is inserted, the formula will automatically be applied to it as well.

Syntax for ARRAY FORMULA: = ARRAYFORMULA( Array_Formula ), where, the parameters include:

  • a cell range as reference.
  • mathematical calculative expression using ranges having the same cell size.
  • a function returning a value greater than one cell.

Google Sheets Array Formula Examples

Let’s visualize an array as a matrix that has a set of organized values in the form of rows and columns. Let’s consider the following table as an example. The set of values present within A1 to D3 is an array with 3 rows and 4 columns. The Array formula is applied to these elements of the array to perform multiple calculations and return a single value or a set of values in the form of a new array.

Let’s say we want to find the grand total of sales without creating an extra column. Follow these steps:

  • Step 1: Click on the cell where you wish to display the result of the grand total of sales.
  • Step 2: Next, enter the syntax =SUM( ARRAYFORMULA( B2: B5* C2: C5) ) and hit Enter.

Array sample 1

Matrix Calculations using Array Formula

Well, did you know we can perform matrix multiplications using the Array formula as well? Let’s take an example to help us understand this better. Consider we have a row matrix called Table1 having 5 rows and a column matrix named Table2 having 4 columns.

  • Step 1: To calculate the product of these two matrices, click on the cell where you wish to map the new result matrix and type the array formula: =ArrayFormula(C2:F2*B1:B5).
  • Step 2: Press Enter and the result is going to be a 5×4 matrix that has the product of Table1 and Table2:

array eg 1

Use of COUTIF and SUMIF Array Formula Google Sheets

Array formulas can also be used with aggregation functions like SUMIF, SUMIFs, COUNTIF, and COUNTIFs, which help in quickly summing up values from a range after matching certain conditions.

Let’s take an item-wise sales table as an example here:

sumif countif step 1

Our aim is to calculate the total sales with respect to each item and display the final results in cell locations F3:F5. If we use a SUMIF function here, we need to apply the formula thrice, i.e., once for each item, since the SUMIF function only satisfies one condition at a time. Using the Array Formula, we can specify a range of cells in the given condition as follows:

=ArrayFormula( SUMIF( B2:B10, A12:A14, C2:C10) )

sumif countif step 2

The second parameter says A12: A14 to help us achieve results for all three items at once! Since SUMIFs and COUNTIFs return the sum of an array after satisfying multiple criteria, the result only needs to be a single value. Hence, we can’t use them directly. Instead, if we use the ampersand operator (&) with our array formula, then we can expand our SUMIF function over the entire column.

The formula must be: =ArrayFormula( SUMIF( A2: A10& B2: B10, B12: B14& A12: A14, C2: C10) ), where:

  • The item in column B must match the item in the corresponding cell in column E.
  • The Outlet in column Column A must match with the outlet in column F.
sumif countif step 3

Here, we simply combined the range cells with their corresponding criteria by using the ampersand & operator first and then wrapped an Array Formula function along with the SUMIF formula. Now, the SUMIF function shows results based on more than one criteria.

Using Google Sheets Array Formula VlookUp

The VLOOKUP function is mainly used to sort an array and search for specific information based on a given condition. The syntax we need to follow here is: =VLOOKUP( search_key, cell_range, index, [ is sorted ] ).

Let’s take the following table, for example, where we see records on various computers based on their type, colour variant, cost, etc. Let’s say we want to extract the colour of the PC by putting its type as an input. For example, take the F5 cell, for example, to input the PC type and then go to the G5 cell and type the formula: =VLOOKUP( F5, A2: D6, 2,0 )Now, type the PC type on F5 and get its colour displayed on the G5.


What Is the Google Sheets Array Formula To Ignore Blank Cells?

While entering data manually onto your Google Sheets, you may leave some spaces blank mistakenly.

blank cells ignore step 1

These blank spaces, if not paid attention to, may clash with the array formulas and result in incorrect outputs. Let’s see the steps to remove such blank rows:

  • Step 1: Select the entire table and then click on the Data menu, followed by Create a filter. This will create a filter for all the column heads.

blank cells ignore step 2

Step 2: Click on any one of the filters, select only (blanks) from the drop-down menu that appears, and click Ok. This will extract and display only the blank cells.

blank cells ignore step 3

Step 3: Next, select all 4 rows and click on clear the rows from to 15.

blank cells ignore step 4

Step 4: Next, again click on one of the filters and click on Select All followed by the Ok button.

blank cells ignore step 5

Your Array table is now ready with no blank cells.

blank cells ignore step 6

What Is the Array Formula to Concatenate in Google Sheets?

Concatenation in an array basically means combining the values of two arrays adjacently. We can use array formulas here to combine columns from multiple arrays and concatenate the values contained in them. Let’s take a table having a list of first and last names as an example.

array concatenate

Our aim is to combine these two columns and concatenate their values to give us a new column named Full Name. Follow these steps:

  • Step 1: Create a new column with the name Full name and click on the cell from where you wish to display the concatenated column.
  • Step 2: Next, type the formula =ARRAYFORMULA( A2:A7& ” “& B2:B7) and press Enter. 

The final result will be a new column with the first name and last name combined, along with a space in between. We can use the concatenation operator for numbers, dates, or any type of special character as well.

array concatenate step 2

Fix Array Formula Not Working Google Sheets

Always make sure you are using the latest version of Google Sheets to work with. If you are using a version of Excel prior to 2019, the Ctrl+Shift+Enter method will not work to enter the Array Formula. Older versions of Google Sheets also do not support the aggregate and sum product functions. Also, if you see your spreadsheet is crashing, try to refresh your page by pressing the keyboard F5 key and starting again.

Recommended Reading On: Java Program to Print Zig-Zag Matrix Number Pattern

Leave a Comment