FILTER and ARRAYFORMULA in Google Sheets (2024 Guide)

FILTER in Google Sheets Explained

The FILTER function is used to filter a range of data based on certain criteria. 

The basic syntax is: 

FILTER(range, condition1, [condition2, ...])

• range is the area of cells that you want to filter.
• condition1 is the condition that the data must meet to be included in the result.
• [condition2, ...] are optional additional conditions.

ARRAYFORMULA in Google Sheets Explained

ARRAYFORMULA enables you to output a formula over a range of cells, applying the formula to each cell in the range. This is particularly useful for expanding formulas that would otherwise only output a single value. 

The basic syntax is:

ARRAYFORMULA(array_formula)

• array_formula is the formula you want to apply over an array/range.
  
  

How to Use ARRAYFORMULA with FILTER in Google Sheets

While FILTER inherently processes arrays, there are scenarios where you might want to use ARRAYFORMULA with FILTER, especially when incorporating functions that don't automatically apply to each cell in a range or when you need to perform operations on the filtered results.

Suppose you have a dataset in A1:B10 where column A contains names and column B contains scores. 

You want to filter scores above 50, multiply them by 2, and then add 10 to each of these scores. You can use the following formula:

=ARRAYFORMULA(FILTER(B1:B10, B1:B10 > 50)*2 + 10)

We hope that this article has helped you and given you a better understanding of how to combine FILTER with ARRAYFORMULA in Google Sheets. If you enjoyed this article, you might also like our articles on how to do a chi square test in Google Sheets and how to group tabs in Google Sheets.