# How to use AVERAGE inside ARRAYFORMULA in Google Sheets

I want to use the `ARRAYFORMULA` version of

``````=AVERAGE(Linda!B3, Nandi!B3, David!B3, Remmelt!B3)
``````

I want the cell to calculate the average value of the entries in that cell from the other tabs (Linda, Nandi, David, Remmelt). I need to use `AVERAGE` and not just sum and division, because I need to deal with the fact that not everyone will enter values in all cells.

I tried using

``````=ARRAYFORMULA(AVERAGE(Linda!B3:B, Nandi!B3:B, David!B3:B, Remmelt!B3:B))
``````

but then it average over all four columns, not just the four cells I want.

As far as I understand `ARRAYFORMULA` takes an input of type array and displays it over as many cells. So the problem comes down to making `AVERAGE` (or similar) output an array.

## 2 Answers

Aggregation functions like AVERAGE, SUM and other similar that get as parameters array of values and just return a single value are not able to return array of values by using ARRAYFORMULA.

One alternative is to use the arithmetic operators (+,-,/,*).

If it makes sense to assume that

• all ranges are of the same size
• there aren't blank cells between non-blank cells
• blank cells at the bottom should be ignored

then the following formula could be used to calculate the average:

``````=ARRAY_CONSTRAIN(
Linda!B3:B+Nandi!B3:B+David!B3:B+Remmelt!B3:B,
1,
COUNT(Linda!B3:B)
)
/
COUNT(Linda!B3:B)
``````

Some "statistical" and "math" type functions - AVERAGE, COUNT, MAX, MIN, SUM - have corresponding `*IF` and / or `*IFS` variants that, when combined with the INDEX function, seem to satisfy this sort of need.

For example, I have a sheet named `monthly` with dates in column A and dollar amounts in column B. I wanted to get the average of dollar amounts for rows where the date was in the year 2021.

Combining INDEX and AVERAGEIF worked for me ...

``````=INDEX(AVERAGEIF(YEAR(monthly!\$A\$2:\$A), 2021, monthly!\$B\$2:\$B))
``````

Functions of this type include ...

Singular forms (`*IF`) match rows based on a single criterion. Plural forms (`*IFS`) match rows based on multiple criteria.

• Are you saying that `=INDEX(AVERAGEIF(YEAR(monthly!\$A\$2:\$A), 2021, monthly!\$B\$2:\$B))` works like an arrayformula? – Tedinoz Apr 26 at 6:36
• @Tedinoz, yep, it's true. But it's not like `ARRAYFORMULA`. Just the first argument of `INDEX` is iterable. And this works like `ARRAYFORMULA`. – contributorpw May 31 at 2:03
• @rh-becker it's doesn't work like expected. – contributorpw May 31 at 2:07