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.


filterif( list, condition)


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.

Example 2


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

Last updated