How To Use Array In Google Sheets. The ArrayFormula Function in Google Sheets may be used to provide a new level of analysis to your spreadsheets.
You may improve your performance in Google Sheets in a variety of ways using the ArrayFormula function, such by:
- Removing tiresome manual labour
- Cleaning up and streamlining your spreadsheets
- Streamlining your reasoning and formulas
Of fact, these advantages are only the tip of the iceberg. The ArrayFormula function in Google Sheets is described in detail below, along with a list of its advantages and use examples.
Table of Contents
ArrayFormula function syntax
The ArrayFormula function’s syntax is as follows:
=ARRAYFORMULA(array_formula)
The array_formula parameter can be:
- A range
- A formula for mathematics that makes use of equal-sized ranges
- A procedure that produces results larger than a single cell
An ArrayFormula may be added to already existing routines. To create an ArrayFormula around your function in Google Sheets, use Cmd/Ctrl + Shift + Enter.
How To Use Array In Google Sheets
ArrayFormulas: Basic
Take a look at this dataset, which includes the prices and subscription counts for four distinct items.
Column D should include a formula that multiplies columns B and C to get the total cost of the four goods.
After that, add an SUM function to cell D6.
The ArrayFormula combines the whole procedure into a single arrayed formula, allowing you to bypass these phases.
Use the following ArrayFormula for this example:
=ArrayFormula(SUM(B2:B5 * C2:C5))
Two ranges (or two arrays) will be part of your formula:
= B2:B5 * C2:C5
The following error will appear if you put this array-based formula exactly as is:
You must instruct Google Sheets that you want the formula to be an ArrayFormula in order to fix this.
=ArrayFormula(B2:B5 * C2:C5)
spreadsheet with arrayformula formulas
The equation is now valid.
Note: If the two array sizes don’t match, array formulas won’t function. The example dataset has four numbers, enabling multiplication of each row.
The SUM function is the last step, and it is used to sum the four values the ArrayFormula returned.
=ArrayFormula(SUM(B2:B5 * C2:C5))
The end result is shown here:
In addition, you may do the computation using the SUMPRODUCT formula while using the array values as inputs.
=SUMPRODUCT(B2:B5,C2:C5)
Formula for arrays with the IF function
Ranges (arrays) may be used with non-array functions. The IF formula may be made an ArrayFormula to do this.
Say you have the following dataset:
Additionally, you utilise an IF statement to determine if a value in column A exceeds $2,000
=IF(A2>2000,”Yes”,”No”)
To perform the test for each row, you copy the formula down.
Use an ArrayFormula at the head of the column as an alternative method to do this. The remaining rows should then be subject to the IF statement.
Rows 2 through 10’s values should all be included in a single array formula:
=ArrayFormula(IF(A2:A10>2000,”Yes”,”No”))
Rows 2 through 10 of the output array, which is created by the single formula in cell 2, include Yes/No responses.
That is the strength of the ArrayFormula and IF function combination
Also Read: How To Farm Gems In YuGiOh Cross Duel
Formula for an array with COUNTIF and SUMIF
To rapidly add numbers inside a range, you may use array formulas with aggregation methods like SUMIF and COUNTIF. Let’s imagine your dataset includes sales data for each product across all of your company’s locations.
The data will appear in cells F3 through F5. Next, calculate the total sales for each item.
Since the SUMIF function only allows you to specify one cell at a time, you will need to apply the formula three times (one for each item) if you use it.
ArrayFormula, however, allows you to define a range of cells:
=ARRAYFORMULA(SUMIF(B2:B10,E3:E5,C2:C10))
The results will appear as follows:
To get the answers for all three items at once, provide the range of cells E3:E5 in the formula.
Like you did with SUMIF, combine the COUNTIF function with ArrayFormula to determine how many entries there are for each item.
Although you will still only receive one result if you mix COUNTIFS or SUMIFS with an Array Formula. But what if you need more than one result?
The secret is to make your formula more precise within the SUMIF function. You must use an ArrayFormula and the ampersand (&) operators in order to employ several conditions inside an SUMIF function.
For instance, your method would be as follows if you wanted to determine the overall sales for each product in the UK:
=ARRAYFORMULA(SUMIF(A2:A10&B2:B10,F3:F5&E3:E5,C2:C10))
But these two prerequisites must continue to exist:
- The cell in column E that corresponds to the item name in column B must have the same name.
- The total numbers in column F should line up with the location in column A.
You may allow the SUMIF function to extend its results to the full column by include the ArrayFormula and (&) operators. This enables you to test for several criteria.
Features Of Array In Google Sheets
There are several advantages to using Google Sheets’ ArrayFormula function, including the following:
- ArrayFormula’s Batch Size Calculations: The ArrayFormula function enables you to utilise a single formula for your entire dataset, regardless of its size, for batch size computations. You may lessen performance concerns brought on by an excessive number of formulae and computations in your spreadsheet.
- Flexibility And Extensibility: Any change to a data range cell will instantly change how the ArrayFormula calculates. Cell values and values that depend on these cells are readily editable.
- Dynamic Performance: You won’t need to manually enter another function since an ArrayFormula function applies to each new row that is inserted within of your cell ranges. You may prevent mistakes and save time by doing this.
ArrayFormula is useful for spreadsheets that include precise and current data, such as weekly sales reports or sales pipeline dashboards in Google Sheets, according to our experience with our own clients.
Deploy ArrayFormulas to concatenate text
You may join columns and concatenate the text inside them using ArrayFormula. Using a dataset containing a list of first and last names will be helpful.
To merge the two columns, use the concatenation operator in your ArrayFormula like follows:
=ARRAYFORMULA(A2:A7&” “&B2:B7)
The first and last names should be merged into one column with a gap between them.
The concatenation operator in your ArrayFormula may also be used to swiftly combine text with special characters, dates, and integers.
What does Google Sheets’ ArrayFormula function do?
Every cell in your predefined data range is applied with a single formula via the ArrayFormula function in Google Sheets. By converting your original formula into an array, the function enables you to create a single formula and apply it to several rows.
The basic idea is simple: enter the formula in the first cell, choose the size of the array, and the function will enter the formula into each cell. The values from cell ranges are produced by the ArrayFormula function into several columns and rows.
The function is extendable, unlike normal formulae, and iterates on additional data immediately. Additionally, you may use ArrayFormula with additional operations like VLOOKUP, FILTER, IF, or SUMIF.
The SUM function is the last step, and it is used to sum the four values the ArrayFormula returned.
SUM (B2:B5 * C2:C5) is an array formula.
The end result is shown here:
In addition, you may do the computation using the SUMPRODUCT formula while using the array values as inputs.
=SUMPRODUCT(B2:B5,C2:C5)
