Of all the unique formulas available in Google Sheets (unique with respect to good old MS Excel), I find ARRAYFORMULA the most daunting. Yet, more often than not, I end up using it. The purpose of this blog is to help you use it too and make best use of it’s full potential.
Why use ARRAYFORMULA at all ?
Let me be very clear : life can go on pretty well even without ARRAYFORMULA. If Excel could survive, so could you! However, we always want that extra bit. With Excel, I always have the problem of having to drag down formulas to include new Cells. Consider the below example:
If we add new values in cell A5 & cell B5, will the sum populate automatically in C5? Hell, no! To make that happen, we need to drag the formula down. I personally don’t like it much . Here’s why. I could have a sheet where the columns A & B auto-populate (from an external source). I would want my spreadsheet to be smart enough to apply the formula automatically.
Google Sheet, without ARRAYFORMULA is as naive as excel. In other words, it wouldn’t be able to drag down the formula on it’s on. ARRAYFORMULA , however changes the game. It allows us to just enter the formula once in cell C2 & the rest happens.
How to use ARRAYFORMULA in Google Sheets?
Unfortunately , the syntax of ARRAYFORMULA isn’t too straight forward. However, with some context I am sure it would be easy to comprehend. Let’s get to understanding the very basics:
- Google Sheets consider values in a row or a column to be single dimensional arrays.
- When you add arrays , subtract one array from another , multiply arrays etc. … you end up getting an array.
- To operate between arrays , each array has to be of the same size.
If you understood the above concept, you might already be guessing what the ARRAYFORMULA syntax would be like:
- Instead of cell references, you would have to pass ROW or COLUMN references.
- The output is an array that would fill up a ROW or COLUMN unlike an ordinary formula that fills up just a cell.
Let’s revisit the above example. To ensure, that the 2 values get added without worrying about dragging the fomula, we need to use the following formula in cell C2:
What did we do ? We are telling Google Sheets to add each element of column A (starting from A2 , since A1 contains the header) with the corresponding cell in column B. Thus, when new values are populated in A5 & B5 , they automatically get added in cell C5.
Most , importantly you will notice that the formula is present only in cell C2. If you click on C3 or C4 – the formula bar above would show the number & not the formula.
Replacing SUMPRODUCT with ARRAYFORMULA
You may be aware of the popular SUMPRODUCT formula. It helps first multiply corresponding values & then add the products. It’s really useful , when you are doing something like weighted average.
Here’s an example, consider the below table:
Sumproduct on the above the table will compute the following: (20*0.2)+(30*0.1)+(45*0.5)
We can get this done using the below ARRAYFORMULA operation :
The formula here, first generates the array of products. Then the sum operator adds up all these products. The result in this case in a single cell.
This actually brings us to an important discovery. When we wrap a function around the column inputs in an ARRAYFORMULA, the function acts on the resultant array (& not the input arrays).
If instead of having a “*” operator between the input columns, we used a comma, would it act the same way as adding 2 columns. Actually, no. The sum function would first act on the first column (adding all the values) & then it would act on the second column (adding all the values) & it would sum it all together.
In other words, the “sum” function (like any other function) would act on all columns it has access to individually & then act on the resultant. In the first case – it just had access to the result column.
Using ARRAYFORMULA with IF condition
We can apply, IF condition to the input-arrays used in an ARRAYFORMULA operation. Consider the below example:
We want to use ARRAYFORMULA to calculate the daily CTR (Click through Rate) of a website. CTR is defined as follows:
CTR = Clicks/Impressions
Let’s imagine that Impressions are stored in Column A of a Google Sheet & clicks are stored in Column B. In C2 , we could apply the following formula:
Here’s what the output looks like:
I hope you are on the same page as me : The #DIV/0! errors make the sheet look really unclean. What’s the cause of this error ? It’s because , there are no values of impressions row6 onwards – thus errors get stored in the result array. If we want to make the sheet clean, we can pass an IF condition that does the following:
- Calculates CTR , when Impressions are greater than 0
- passes blank when Impressions are 0
Here’s what the formula looks like:
FAQs : Arrayformula Google Sheets
While a normal formula outputs in a single cell, an Arrayformula outputs in a range of cells.
In Google sheets, arrayformula is an available function. You can use it directly. Alternately, the short-cut key combination of Ctrl+Shift+Enter (while editing a formula) directly adds arrayformula to the beginning of the formula.