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

Returns an array.

Example 1

sum(filterif([United States].DESCENDANTS, ROW.LABEL = 'Soda'))

The formula above will use FILTERIF to filter all the rows under Region 'United States' with category = 'Soda'. It will then sum up all these values column-wise.

FILTERIF usage

Example 2

average(filterif([Column1,Column2,Column3],">1000"))

Returns the average of values in Column1, Column2, and Column3 if they are greater than 1000