|
High-Performance FILTER.IFS family of functions
The FILTER.IFS, FILTER.MATCH, FILTER.SORTED, ASUMIFS and ACOUNTIFS functions provide you with a high-performance, high-function solution to multiple criteria problems that previously required slow SUMPRODUCT or Array formulas.
Outstanding performance improvements with sorted data.
The FILTER.IFS family of functions has been implemented using ultra-efficient binary search algorithms that give stunning performance on sorted data.
Efficient performance with clustered or sparse results from unsorted data.
Special care has been taken to minimize search time for unsorted data by exploiting clustered data and subsets of results. For many cases this gives substantial performance improvements on unsorted data.
For worst-case data (50% of data is results randomly selected from unsorted input data) performance will be comparable to or slightly worse than SUMPRODUCT.
Efficient handling of full-column criteria.
Processing is restricted to the used range rather than explicitly checking every row in the column.
Give Multiple-criteria ability to other Excel Functions and UDFs
You can embed the FILTER.IFS functions inside virtually any built-in or UDF function that can handle a Range as input. This extends multi-criteria function to an incredible range of functions, for example:
LISTDISTINCTS, COUNTDISTINCTS, MAX, MIN, SUM, COUNT, COUNTA, AVERAGE, MEDIAN, MODE, LARGE, INDEX, VAR, RANK ….
Built-in OR to eliminate double-counting.
Sets of Criteria can be separated by #OR#. The results from multiple sets of criteria are OR together but never double-counted as can happen when using SUMPRODUCT(…) + SUMPRODUCT(…)
Use Lists for alternate criteria
Where you have multiple possible criteria for a single column (FRUIT can be Apples, Oranges or Pears) you can use either a reference to a Range containing the alternatives or an array of constants. You can even use different conditions for each element in the List (MonthNumber=2 or >8).
Lists can be inclusion or exclusion lists.
Wild-Card and Regular Expression pattern-matching criteria
You can use wild-card and regular expression patterns for string criteria. The patterns can look for combinations of characters and numbers using powerful pattern-matching function.
Wide variety of Criteria Operators
In addition to the usual criteria operators =, <, <=, >, >=, <> you can use
~ (Like), ~~ (Regular Expression), True, False,
And Data Type filters #ERR, #TXT, #N, #BOOL, #EMPTY, #ZLS, #TYPE, #BLANK
Prefixing any of the criteria operators with ¬ makes the criteria an exclusion criterion rather than an inclusion criterion.
Use Column Labels or Column Numbers for Criteria
Using Column labels from the first row of the data makes the FILTER functions easier to read and you don’t have to remember to change the column numbers in all your formulas when you add columns.
Create virtual calculated criteria columns.
Unlike SUMIF and COUNTIF you can use expressions containing Excel functions and formulas to create virtual calculated columns for your criteria columns.
|
