FILTERIF

The FILTERIF function can be used to filter an array of values based on a given condition. It returns an array—hence, it has to be used in conjunction with an aggregate function like SUM or AVERAGE. It can be used to populate a calculated row.

Syntax

FILTERIF(list, condition)

Arguments

list – The input list of values on which the filter is to be applied.

condition – The condition to be evaluated.

Return value

The function returns an array.

Example 1

FILTERIF([AGE], ">50")

The formula above will use FILTERIF to filter all the rows of people whose age exceeds 50.

Example 2

AVERAGE(FILTERIF([Column1,Column2,Column3],">1000"))

The above example returns the average of values in Column1, Column2, and Column3 if they are greater than 1000.