Google Sheets Array Formula Example and Usage

0
78
array function and formula in google sheets

Before going to the Array formula example and usage of it in Google Sheets, let me explain what an Array formula is. Without learning Google Sheets ARRAYFORMULA you cannot be an advanced user of it. I will make you understand what ArrayFormula is with a simple example first.

Array Formula Quick Tip

You can convert a normal formula or a non array formula to an array formula very easily. Normally you press the Enter key after entering any formula in a cell. Here instead of pressing the enter key, press Ctrl+Shift+Enter to make it an array formula. See the below array formula example and you can understand this concept.

Google Sheets Array Formula Example

See the sample data set below which is showing the marks of three students, student “A”, “B” and “C”, in an exam in four different subjects.

Google Sheets Array Formula Example

When you want to total the marks scored by any of the students in all subjects, normally you use the formula as below.

=B2+C2+D2+E2

The above formula can sum the total mark scored by student “A” in all subjects. You can see the same marked on the above image.

So what we do when we want to find the total marks scored by other students? We simply copy and paste the formula in Cell F2 (as per our above example data set) to cell F3 and then to F4.

When the number of students are more this copy and paste can slow down your spreadsheet because of the number of formulas existing. Also when you change the formula in Cell F2, you need again to copy and paste the formula to below cells as it won’t change automatically. You can overcome the above disadvantages by applying array formula.

Once again see the formula above. To apply Array Formula, what you need to do is, after entering the formula, instead of pressing enter, press Ctrl+Shift+Enter. It will automatically apply the Array Formula.

apply ctrl+shift+enter to change an existing formula to an array formula

You have not yet finished. Why? It’s simply because this array formula only covers the following one formula, i.e.,

=B2+C2+D2+E2

But we have same formula in cell F3 and F4 as;

=B3+C3+D3+E3
and
=B4+C4+D4+E4

So we need to include these ranges in our array formula. So the result will automatically be expanded to the below adjoining cells. No need to apply array formula in Cell F3 and F4. In cell F2 use the array formula as;

=ArrayFormula(B2:B4+C2:C4+D2:D4+E2:E4)

Below you can see the array Formula usage when you want to include infinitive rows.

=ArrayFormula(B2:B+C2:C+D2:D+E2:E)

Here in the case of infinitive rows, you should apply If function with Array formula to avoid blank rows in formula. A littler variation to the above Array formula.

=ARRAYFORMULA(if(A2:A<>””,B2:B+C2:C+D2:D+E2:E,””))

I have an example to the practical use of Google Sheets Array formula on Info Inspired with a payroll hours calculation. Check that and then move to below.

Google Sheets Array Formula Use with Text Function

I’m not going much details about the advanced use of it. Just one example that can give you an insight in to the advanced of use ArrayFormula in Google Sheets. I may write a specific tutorial on the advanced use of Array Formula later as it may confuse you at this stage.

See how can we use Array Formula with Text function in Google Sheets.

How to use Array formula with Text function in Google Sheets.

Example:

You can combine text with text, text with number or text with date in Google Sheets. So here also we an apply array formula as below.

Array Formula with Text function in google sheets

You only need to apply the formula in cell L2 as it will automatically be expanded to other cells below.

Hope you learn the use of Array Formula in Google sheets with the above examples.

LEAVE A REPLY

Please enter your comment!
Please enter your name here