|
The FILTER.IFS Multiple Criteria Function Family
The FILTER.IFS, FILTER.SORTED, FILTER.MATCH, ASUMIFS and ACOUNTIFS functions are a family of high-performance SpeedTools functions you can use to replace many SUMPRODUCT and array functions.
The FILTER.IFS functions are extremely fast when used on sorted data or well-structured data.
Data can be sorted ascending, descending or unsorted.
Use FILTER.IFS inside functions like RANK, MAX, MIN, SUM, COUNT, COUNTA, AVERAGE, MEDIAN, MODE, LARGE, INDEX (any function or UDF that will handle a range or array input), or in a multi-cell array formula to return the resulting filtered subset of data.
Condition/Criteria operators can be
Relational Operators =, >=, <=, >, <, <>, True, False
~ means Like pattern matching (VBA Like), ~~ means regular expression matching
Data Type Criteria filters #ERR, #TXT, #N, #BOOL, #EMPTY, #ZLS, #TYPE, #BLANK
All Criteria can be inverted using the Not prefix ¬
A list of alternative inclusion or exclusion conditions can be given as an array of Constants or Range Reference
The Criteria column being compared to the Condition/criteria can be
A column range within or outside the DataRange
A Calculated column range (an expression that evaluates to a column)
An array of Constants
A row is included when ALL of a set of conditions are met.
Sets of Conditions can be separated into #OR# blocks
Columns within the DataRange can be identified either by a label in the first row, or by a column number, or directly using a range reference.
Optimizing the performance of the FILTER.IFS family of functions
FILTER.IFS is faster with sorted data than unsorted.
FILTER.IFS will exploit grouping or structuring of unsorted data.
The sequence in which the Criteria/Conditions are given can have a significant influence on performance.
Where some criteria apply to the sorted column(s) and some are unsorted, specify the sorted ones first.
For both Sorted and Unsorted data the first criteria should be the one that eliminates the largest number of rows.
For both sorted and unsorted criteria specify = criteria before other types of criteria.
Where there are 2 criteria applying to the same column (for example data between 2 dates) the criteria should be adjacent.
For Unsorted data FILTER.IFS will be most efficient when the first criterion results in a relatively small number of groups of row meeting the first criteria
The worst case for FILTER.IFS is unsorted data where the first criterion eliminates every other row.
|
