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 calculates the average of values in Columns 1, 2, and 3 that are greater than 1,000.