FILTER.IFS function

FILTER.IFS returns the cells from the chosen column(s) in the input data range that match the extended conditions or criteria. The syntax for the criteria is similar to SUMIFS and COUNTIFS but with considerably more options.

FILTER.IFS can be used either to add multiple conditions to aggregating functions like LISTDISTINCTS.SUM, SUM, MEDIAN, RANK etc. or as a multi-cell array formula.

FILTER.IFS is most efficient when used with sorted data.

FILTER.IFS  Syntax

FILTER.IFS(nsortedCols,InputRange,ReturnCol,CriteriaColumn1,Criteria1,
CriteriaColumn2,Criteria2, …, ["#OR#", nsortedCols,] CriteriaColumnx, Criteriax, …)

Example: SUM(FILTER.IFS(3,$A$3:$F$10303,"Value","FromCtry","=" & $H6,"ToCtry","=" & $I6,"ToCity","=" & $J6,"Date",">"&$K6,"Date","<"&$L6))

FILTER.IFS Parameters

nSortedCols (required for all the FILTER.IFS family functions except FILTER.SORTED)

If nSortedCols is NOT equal to zero the InputRange must be sorted on the criteria columns in the DataRange.

Gives the number of sorted Criteria columns in the InputRange.

If nSortedCols is 2 then CriteriaColumn1 must be the major sort column in the InputRange, CriteriaColumn2 the next minor sort column in the InputRange, and so on. (In other words InputRange must be sorted by CriteriaColumn2 within CriteriaColumn1).
The sorted criteria columns must all be sorted either Ascending or Descending. Positive nSortedCols indicates sorted ascending, negative nSortedCols indicates descending and zero indicates that the criteria columns are not sorted.
Columns outside the InputRange and Calculated Columns are always treated as unsorted.
Columns with criteria operators  ~, ~~, the Type criteria operators or with a list of criteria given as an array constant or multi-cell range reference are always treated as unsorted.
The first column with a criteria operator other than =, True, False will cause subsequent columns to be treated as unsorted.

Using non-zero NsortedCols with unsorted data will give unpredictable and usually incorrect results.

InputRange (required)

The InputRange must refer to a Range: it cannot be an array or the result of an expression. The reference to the range containing the column from which the subset of results will be returned, and also containing all the sorted Criteria columns. The InputRange may also contain unsorted Criteria Columns.
The InputRange may optionally contain column labels in the first row, in which case at least one of the criteria columns or return column should be identified using a label. If you only use column numbers then the InputRange should NOT include labels.
 

ReturnCol (required for all the FILTER.IFS family functions except FILTER.MATCH)

Specifies the column in InputRange from which the subset of data will be returned. If given as text will be interpreted as a column label in the first row of the InputRange.

If given as a number will be interpreted as a column number within InputRange, and InputRange should NOT include a header row.

If ReturnCol is 0 then all the columns in InputRange will be returned (ASUMIFS and ACOUNTIFS return a row of sums and counts for each column).

CriteriaColumn1 (required)

Specifies the first Criteria Column. Can be text, a number, a Range Reference, an array of constants or an expression that results in a column.

Text will be interpreted as a column label in the first row of the InputRange

Number will be interpreted as a column number within InputRange.

Range Reference will be interpreted as an independent column outside InputRange.

An array of constants will be interpreted as a column of data.

An expression will be evaluated by Excel as a calculated column before being passed to the FILTER function.

If any of the Criteria Columns are calculated columns (contain expressions) then the FILTER function must be array entered (Control-Shift-Enter).

Criteria Columns must contain the same number of rows as the InputRange, but allowance is made for a row of column labels in either or both the InputRange and Criteria Columns.

Criteria1 (required)

Specifies the criteria expression to be applied to CriteraColumn1.

The Criteria expression must resolve to a text string starting with a criteria operator and ending with a Criteria value.

If no criteria operator is found at the start then = will be assumed.

A Criteria expression may be any Excel expression containing strings, range references, defined names and operators

The maximum number of CriteriaColumn criteria pairs is 40

Criteria Operators

Valid Criteria operators are:

The relational operators:

= is equal to

> is greater than

>= is greater than or equal to

< is less than

<= is less than or equal to

¬ is not equal to

If no operator is given it is treated as =, except that the data type is never converted (see Data Types section below).

The Boolean operators:

True the cell value is true

False the cell value is false

The Pattern operators:

~ Like the criteria value pattern using the wild-card characters * and ?

~~ Matches against a regular expression pattern

Because Excel propagates error values through formulas and expressions the above criteria operators have no effect when preceding an error value.

The Data Type Operators:

#TYPE the data type of the cell is the same as the first data cell in the criteria column

#ERR the cell contains an error value

#TXT the cell contains a string (text) value

#N the cell contains a number (integer or with decimal point, date, time, currency)

#BOOL the cell contains a Boolean True or False

#EMPTY the cell is empty

#ZLS the cell contains a zero length string

#BLANK the cell contains a string of 1 or more blanks/spaces

Any of these criteria operators can be prefixed by ¬ (not) to convert the operator from an inclusion operator to an exclusion operator.

Data Type Comparison

Excel has 5 fundamental data types: Number (integers, real numbers, dates, times, currency), String (text), Boolean (true or false), Error (#NA etc) and Empty (unused cells).

When a column of mixed data types are sorted Excel uses the following comparison relationships:

Numbers < Strings < Booleans < Errors

Empty cells are always sorted last, both in an ascending and a descending sort.

The SpeedTools FILTER functions use the same comparison relationship between data types.

Data Type Conversion

When a criteria value is preceded by any of the criteria operators its data type is not known (Criteria values without any preceding criteria operator have a specific data type).

For example in “<1234” the 1234 could be a number or could be a string. When this criteria value is being compared to a criteria column that could contain multiple data types the FILTER.IFS functions use the following rules:

When the criteria operator is ~ or ~~ the criteria value and the values in the criteria column will be converted to strings before doing the pattern match.

Otherwise the preferred data type will be the data type of the first data (non-header) cell in the criteria column. FILTER.IFS will attempt to convert the criteria value to the preferred data type. If this is not possible FILTER.IFS will convert the criteria value to a number, then a string, then a Boolean then an error.

Overriding Data Type Conversion using the & Prefix

If your criteria column contains mixed data types (for instance both numeric and string numbers) you can ask FILTER.IFS to try to convert the criteria value to the best possible data type match with each cell in the criteria column.

So FILTER.IFS would use the numeric version of the criteria value to compare with a numeric number and the string version of the criteria value to compare with a string/text number.

You request this by adding a & character as a prefix to the criteria operator (after the ¬ prefix if you are using the exclusion prefix).

Criteria Values

Criteria values and Criteria column values are NOT Case sensitive.

Criteria Like Patterns

Like Patterns can contain:

* Any number of characters, including none

? Any single character

Pattern Examples:

Criteria Lists

Criteria can be given either as a single criterion or a list of alternative Criteria.

A list of alternative Criteria can be either an array of Constants or a Range Reference.

The individual elements in the array or cells in the Range Reference may each start with their individual criteria Operator, or may not have a criteria operator.

If a list of alternative criteria is given it is treated as an OR for each element of the Criteria Column.

If the first element in the list or range is the character ¬ then the list is treated as an exclusion list: values in the criteria column that match any of the items in the list are excluded from the filter.

Criteria Lists Examples:

FILTER.IFS(0,$A$1:$C$6,"Hundreds","Digits",{"Four","One","Two"},"Tens",{"Fifty","Fourty","Thirty","Twenty"})

The Digits column must contain any of Four, One or Two and the Tens column must contain any of Fifty, Forty, Thirty or Twenty.

CriteriaColumn2, Criteria2, … (optional)

You can give additional pairs of Criteria Column and Criteria.

All of the Criteria pairs given in a set must be True for a row of data from the Return Column to be included.

"#OR#", nSortedCriteria, (optional)

If you need alternative sets of criteria you can separate them with “#OR#”, nSortedCols.

A row will be selected from the Return Column if ANY of the alternative sets of Criteria are met.

The row will only be selected once if both alternative sets of criteria are met (no double-counting).

For Example:

CriteriaCol1,Criteria1,CriteriaCol2, Criteria2, “#OR#”,1,CriteriaCol3, Criteria3,CriteriaCol4,Criteria4

A row will be selected from the Return Column if

 (both criteria1 and Criteria2 are True) OR (both Criteria3 and Criteria4 are True)

Note that a Criterion can also contain a list of alternative conditions (see above)

Combining FILTER.IFS Criteria in Logical Combinations

Default is AND

By default, ALL the criteria given must be met for a row to be included in the subset.

Multiple alternatives for a single column

You can use criteria lists to give a number of alternative criteria for a single column (OR).

Alternative Sets of Criteria

Where you have alternative sets of criteria (both Criteria1 and Criteria2 must be True) OR (both Criteria3 and Criteria4 must be True) you can use #OR# to separate them.

More complex Logical Combinations

You can use SpeedTools’s OR.ROWS and AND.ROWS functions to build more complicated calculated combinations of criteria within FILTER (see below).

Copyright © 2023 Decision Models Ltd