Use the ARRAYFORMULA in Google Sheets

The syntax for the function is ARRAYFORMULA(array_formula) where the one and only argument is required. The argument can include a cell range, expression, or function for one or more arrays of the same size.There are two ways to insert an ARRAYFORMULA formula in Google Sheets.

• This first method is ideal when you’ve already typed your formula and realize you want to use the ARRAYFORMULA function instead. Or, for those times when you want to concentrate on the meat of the formula and worry about adding the ARRAYFORMULA function afterward.
• With an ordinary formula entered into a cell, place your cursor in or on the formula in the Formula Bar. Then, press Ctrl+Shift+Enter on Windows or Command+Shift+Return on Mac.

For this first example, we’ll do a simple multiplication calculation for a cell range. We’ll take our Quantity Sold and multiply it by the Unit Price. To do this for our entire array, we would use the following formula:

=ARRAYFORMULA(B2:B6*C2:C6)

While we have a small cell range for our calculation here, cells B2 through B6 multiplied by cells C2 through C6,
imagine if you have hundreds of cells in the array. Rather than inserting a formula that you then need to copy down, 
just use the ARRAYFORMULA for the array.

For this next example, let’s toss in another function. We’ll add the formula for the IF function as the argument for ARRAYFORMULA.
Using the formula below, we will display Bonus if the amount in the cell range F2 through F6 is above 20,000 and No Bonus if it’s not.

=ARRAYFORMULA(IF(F2:F6>20000,"Bonus", "No Bonus"))

Again, we save a step by inserting a single formula that populates for the entire cell range.

In our final example, we’ll combine the SUMIF function with ARRAYFORMULA. Using the formula below, we sum the amounts in cells M2 through M16 if the values in cells O3 through O5 equal those in cells L2 through L16.

= ARRAYFORMULA(SUMIF(L2:L16,O3:O5,M2:M16))

Now with this one simple formula, we’re able to get sales totals for only those three products we want. The formula for the ARRAYFORMULA function fills our cells for Shirts, Shorts, and Shoes correctly.

Here you have the basics of using the ARRAYFORMULA function in Google Sheets. So, you can experiment with more complex formulas to achieve the results you need. Keep in mind this is one of those Google Sheets functions currently unavailable in Microsoft Excel, so take advantage of it!