FILTERIF

The FILTERIF function can be used to filter an array of values based on a given condition. It returns an array and should be used with an aggregate function such as SUM or AVERAGE.

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 uses FILTERIF to filter all rows under Region 'United States' with category = 'Soda'. It will then sum up all these values column-wise.

FILTERIF usage

Example 2

sum(filterif([[Sales],[Sales1],Sales2]],">0.1m"))

Returns the sum of values in Sales, Sales1, and Sales2 if they are greater than 0.1m.

FILTERIF to calculate the total sales