Searches the 'searchField' field of the specified 'table' for the 'v' value and returns the sum of the 'calcField' field values for all found records. If 'v' is a name (text string) specified without quotation marks, it's assumed to be a name of the field from the table where the formula is used. The 'table', 'searchField' and 'calcField' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

NOTE: if the fast binary searching is used, the type of the 'v' parameter must match the type of the searched field. They must be both either textual or numeric. Otherwise an error is returned.

The 'options' argument is a sum of the following values:

• 0 - the 'table' will be searched for 'v' using the fastest binary searches; case-insensitive. This is the recommended option if the source record set contains a large number of records with the 'v' values and the external 'table' has many records to search as well.
• 1 - performs case-sensitive searching.
• 2 - performs string sorting/comparison (hyphen and apostrophe are sorted, as opposed to the default word-sorting).
• 4 - the 'v' value is a regular expression; instead of the fast binary exact searches, slower sequential searching will be used.
• 8 - allows empty matches when using regular expressions.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

=sum_ex("products", ProductID, "ProductID", "ProductName", 0, 0)
=sum_ex("folder1/products", ProductID, "ProductID", "UnitPrice", 4, -1)

Searches the 'searchField' field of the specified 'table' for the 'v' value and returns the minimum of the 'calcField' field values for all found records. If 'v' is a name (text string) specified without quotation marks, it's assumed to be a name of the field from the table where the formula is used. The 'table', 'searchField' and 'calcField' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

NOTE: if the fast binary searching is used, the type of the 'v' parameter must match the type of the searched field. They must be both either textual or numeric. Otherwise an error is returned.

The 'options' argument is a sum of the following values:

• 0 - the 'table' will be searched for 'v' using the fastest binary searches; case-insensitive. This is the recommended option if the source record set contains a large number of records with the 'v' values and the external 'table' has many records to search as well.
• 1 - performs case-sensitive searching.
• 2 - performs string sorting/comparison (hyphen and apostrophe are sorted, as opposed to the default word-sorting).
• 4 - the 'v' value is a regular expression; instead of the fast binary exact searches, slower sequential searching will be used.
• 8 - allows empty matches when using regular expressions.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

=min_ex("products", ProductID, "ProductID", "ProductName", 0, 0)
=min_ex("folder1/products", ProductID, "ProductID", "UnitPrice", 4, -1)

Searches the 'searchField' field of the specified 'table' for the 'v' value and returns the maximum of the 'calcField' field values for all found records. If 'v' is a name (text string) specified without quotation marks, it's assumed to be a name of the field from the table where the formula is used. The 'table', 'searchField' and 'calcField' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

NOTE: if the fast binary searching is used, the type of the 'v' parameter must match the type of the searched field. They must be both either textual or numeric. Otherwise an error is returned.

The 'options' argument is a sum of the following values:

• 0 - the 'table' will be searched for 'v' using the fastest binary searches; case-insensitive. This is the recommended option if the source record set contains a large number of records with the 'v' values and the external 'table' has many records to search as well.
• 1 - performs case-sensitive searching.
• 2 - performs string sorting/comparison (hyphen and apostrophe are sorted, as opposed to the default word-sorting).
• 4 - the 'v' value is a regular expression; instead of the fast binary exact searches, slower sequential searching will be used.
• 8 - allows empty matches when using regular expressions.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

=max_ex("products", ProductID, "ProductID", "ProductName", 0, 0)
=max_ex("folder1/products", ProductID, "ProductID", "UnitPrice", 4, -1)

Searches the 'searchField' field of the specified 'table' for the 'v' value and returns the 1st quartile of the 'calcField' field values for all found records. If 'v' is a name (text string) specified without quotation marks, it's assumed to be a name of the field from the table where the formula is used. The 'table', 'searchField' and 'calcField' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

NOTE: if the fast binary searching is used, the type of the 'v' parameter must match the type of the searched field. They must be both either textual or numeric. Otherwise an error is returned.

The 'options' argument is a sum of the following values:

• 0 - the 'table' will be searched for 'v' using the fastest binary searches; case-insensitive. This is the recommended option if the source record set contains a large number of records with the 'v' values and the external 'table' has many records to search as well.
• 1 - performs case-sensitive searching.
• 2 - performs string sorting/comparison (hyphen and apostrophe are sorted, as opposed to the default word-sorting).
• 4 - the 'v' value is a regular expression; instead of the fast binary exact searches, slower sequential searching will be used.
• 8 - allows empty matches when using regular expressions.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

=quartile1_ex("products", ProductID, "ProductID", "ProductName", 0, 0)
=quartile1_ex("folder1/products", ProductID, "ProductID", "UnitPrice", 4, -1)

Searches the 'searchField' field of the specified 'table' for the 'v' value and returns the median of the 'calcField' field values for all found records. If 'v' is a name (text string) specified without quotation marks, it's assumed to be a name of the field from the table where the formula is used. The 'table', 'searchField' and 'calcField' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

NOTE: if the fast binary searching is used, the type of the 'v' parameter must match the type of the searched field. They must be both either textual or numeric. Otherwise an error is returned.

The 'options' argument is a sum of the following values:

• 0 - the 'table' will be searched for 'v' using the fastest binary searches; case-insensitive. This is the recommended option if the source record set contains a large number of records with the 'v' values and the external 'table' has many records to search as well.
• 1 - performs case-sensitive searching.
• 2 - performs string sorting/comparison (hyphen and apostrophe are sorted, as opposed to the default word-sorting).
• 4 - the 'v' value is a regular expression; instead of the fast binary exact searches, slower sequential searching will be used.
• 8 - allows empty matches when using regular expressions.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

=median_ex("products", ProductID, "ProductID", "ProductName", 0, 0)
=median_ex("folder1/products", ProductID, "ProductID", "UnitPrice", 4, -1)

Searches the 'searchField' field of the specified 'table' for the 'v' value and returns the 3rd quartile of the 'calcField' field values for all found records. If 'v' is a name (text string) specified without quotation marks, it's assumed to be a name of the field from the table where the formula is used. The 'table', 'searchField' and 'calcField' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

NOTE: if the fast binary searching is used, the type of the 'v' parameter must match the type of the searched field. They must be both either textual or numeric. Otherwise an error is returned.

The 'options' argument is a sum of the following values:

• 0 - the 'table' will be searched for 'v' using the fastest binary searches; case-insensitive. This is the recommended option if the source record set contains a large number of records with the 'v' values and the external 'table' has many records to search as well.
• 1 - performs case-sensitive searching.
• 2 - performs string sorting/comparison (hyphen and apostrophe are sorted, as opposed to the default word-sorting).
• 4 - the 'v' value is a regular expression; instead of the fast binary exact searches, slower sequential searching will be used.
• 8 - allows empty matches when using regular expressions.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

=quartile3_ex("products", ProductID, "ProductID", "ProductName", 0, 0)
=quartile3_ex("folder1/products", ProductID, "ProductID", "UnitPrice", 4, -1)

Searches the 'searchField' field of the specified 'table' for the 'v' value and returns the most frequently occurring value of the 'calcField' field values for all found records. If 'v' is a name (text string) specified without quotation marks, it's assumed to be a name of the field from the table where the formula is used. The 'table', 'searchField' and 'calcField' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

NOTE: if the fast binary searching is used, the type of the 'v' parameter must match the type of the searched field. They must be both either textual or numeric. Otherwise an error is returned.

The 'options' argument is a sum of the following values:

• 0 - the 'table' will be searched for 'v' using the fastest binary searches; case-insensitive. This is the recommended option if the source record set contains a large number of records with the 'v' values and the external 'table' has many records to search as well.
• 1 - performs case-sensitive searching.
• 2 - performs string sorting/comparison (hyphen and apostrophe are sorted, as opposed to the default word-sorting).
• 4 - the 'v' value is a regular expression; instead of the fast binary exact searches, slower sequential searching will be used.
• 8 - allows empty matches when using regular expressions.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

=mode_ex("products", ProductID, "ProductID", "ProductName", 0, 0)
=mode_ex("folder1/products", ProductID, "ProductID", "UnitPrice", 4, -1)

Searches the 'searchField' field of the specified 'table' for the 'v' value and returns the number of found records. If 'v' is a name (text string) specified without quotation marks, it's assumed to be a name of the field from the table where the formula is used. The 'table' and 'searchField' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

NOTE: if the fast binary searching is used, the type of the 'v' parameter must match the type of the searched field. They must be both either textual or numeric. Otherwise an error is returned.

The 'options' argument is a sum of the following values:

• 0 - the 'table' will be searched for 'v' using the fastest binary searches; case-insensitive. This is the recommended option if the source record set contains a large number of records with the 'v' values and the external 'table' has many records to search as well.
• 1 - performs case-sensitive searching.
• 2 - performs string sorting/comparison (hyphen and apostrophe are sorted, as opposed to the default word-sorting).
• 4 - the 'v' value is a regular expression; instead of the fast binary exact searches, slower sequential searching will be used.
• 8 - allows empty matches when using regular expressions.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

=count_ex("products", ProductID, "ProductID", 0, 0)
=count_ex("folder1/products", ProductID, "ProductID", 4, -1)

Returns the sum of the 'calcField' field values from the specified 'table' for records that meet the specified list of filters/criteria.

The 'table', 'calcField' and 'field(n)' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

References to field values/names in the same table where the formula resides must be used without any quotation marks.

The criteria can be one of the following:

1. a name of the field in the current table, optionally combined with the =, >, >=, <, <= operators.
2. a text string beginning with the =, >, >=, <, <= operators. Numeric and date/time searches require unformatted numbers and generic date/time strings (YYYY-MM-DD).
3. a number or a search pattern: a text string optionally containing the special characters '?' (any character) or '*' (any string). To search for a literal ? or * character, place a tilde (~) before it. Numbers and dates must be used in the unformatted form.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

NOTE: If some 'criteria' can contain pattern characters (*?~) but you don't want to perform pattern searches, use the '&' operator to prefix that criteria with '='. For example: "=" & CustomerID

NOTE: Unlike the sum_ex() function, the sumIfs_ex() function always performs slower sequential searching. If both the source and target tables contain hundreds of thousands or millions of records at the same time, it might be too slow. You can improve its performance by including the leading "=" before each criteria (to avoid pattern matching) and by increasing the number of used processor cores in the Settings > Options dialog box.

=sumIfs_ex("orders", "Total", "id", CustomerID, 0)

Returns the total sales amount for a given customer. 'orders' is a table with all orders, 'id' and 'Total' are its fields containing customers' IDs and total values for each order, respectively, and 'CustomerID' is a field of the table where the result is placed.

=sumIfs_ex("folder1/orders", "Total", "id", "=" & CustomerID,)

Returns the total sales amount for a given customer; the CustomerID may contain pattern characters, but no pattern search will be performed.

=sumIfs_ex("orders", "Qty", "ProductName", "Tofu", "ProductName", "Chocolade", 0)
=sumIfs_ex("folder1/folder2/orders", "Total", "Date", ">2011-01-01", "Date", "<2011-07-01", 0)

Returns the minimum of the 'calcField' field values from the specified 'table' for records that meet the specified list of filters/criteria.

The 'table', 'calcField' and 'field(n)' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

References to field values/names in the same table where the formula resides must be used without any quotation marks.

The criteria can be one of the following:

1. a name of the field in the current table, optionally combined with the =, >, >=, <, <= operators.
2. a text string beginning with the =, >, >=, <, <= operators. Numeric and date/time searches require unformatted numbers and generic date/time strings (YYYY-MM-DD).
3. a number or a search pattern: a text string optionally containing the special characters '?' (any character) or '*' (any string). To search for a literal ? or * character, place a tilde (~) before it. Numbers and dates must be used in the unformatted form.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

NOTE: If some 'criteria' can contain pattern characters (*?~) but you don't want to perform pattern searches, use the '&' operator to prefix that criteria with '='. For example: "=" & CustomerID

NOTE: Unlike the min_ex() function, the minIfs_ex() function always performs slower sequential searching. If both the source and target tables contain hundreds of thousands or millions of records at the same time, it might be too slow. You can improve its performance by including the leading "=" before each criteria (to avoid pattern matching) and by increasing the number of used processor cores in the Settings > Options dialog box.

=minIfs_ex("orders", "Total", "id", CustomerID, 0)

Returns the minimum sale amount for a given customer. 'orders' is a table with all orders, 'id' and 'Total' are its fields containing customers' IDs and total values for each order, respectively, and 'CustomerID' is a field of the table where the result is placed.

=minIfs_ex("folder1/orders", "Total", "id", "=" & CustomerID, -1)

Returns the minimum sale amount for a given customer; the CustomerID may contain pattern characters, but no pattern search will be performed.

=minIfs_ex("orders", "Qty", "ProductName", "Tofu", "ProductName", "Chocolade", -1)
=minIfs_ex("folder1/folder2/orders", "Total", "Date", ">2011-01-01", "Date", "<2011-07-01", 0)

Returns the maximum of the 'calcField' field values from the specified 'table' for records that meet the specified list of filters/criteria.

The 'table', 'calcField' and 'field(n)' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

References to field values/names in the same table where the formula resides must be used without any quotation marks.

The criteria can be one of the following:

1. a name of the field in the current table, optionally combined with the =, >, >=, <, <= operators.
2. a text string beginning with the =, >, >=, <, <= operators. Numeric and date/time searches require unformatted numbers and generic date/time strings (YYYY-MM-DD).
3. a number or a search pattern: a text string optionally containing the special characters '?' (any character) or '*' (any string). To search for a literal ? or * character, place a tilde (~) before it. Numbers and dates must be used in the unformatted form.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

NOTE: If some 'criteria' can contain pattern characters (*?~) but you don't want to perform pattern searches, use the '&' operator to prefix that criteria with '='. For example: "=" & CustomerID

NOTE: Unlike the max_ex() function, the maxIfs_ex() function always performs slower sequential searching. If both the source and target tables contain hundreds of thousands or millions of records at the same time, it might be too slow. You can improve its performance by including the leading "=" before each criteria (to avoid pattern matching) and by increasing the number of used processor cores in the Settings > Options dialog box.

=maxIfs_ex("orders", "Total", "id", CustomerID, -1)

Returns the maximum sale amount for a given customer. 'orders' is a table with all orders, 'id' and 'Total' are its fields containing customers' IDs and total values for each order, respectively, and 'CustomerID' is a field of the table where the result is placed.

=maxIfs_ex("folder1/orders", "Total", "id", "=" & CustomerID, -1)

Returns the maximum sale amount for a given customer; the CustomerID may contain pattern characters, but no pattern search will be performed.

=maxIfs_ex("orders", "Qty", "ProductName", "Tofu", "ProductName", "Chocolade", 0)
=maxIfs_ex("folder1/folder2/orders", "Total", "Date", ">2011-01-01", "Date", "<2011-07-01", 0)

Returns the first quartile of the 'calcField' field values from the specified 'table' for records that meet the specified list of filters/criteria.

The 'table', 'calcField' and 'field(n)' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

References to field values/names in the same table where the formula resides must be used without any quotation marks.

The criteria can be one of the following:

1. a name of the field in the current table, optionally combined with the =, >, >=, <, <= operators.
2. a text string beginning with the =, >, >=, <, <= operators. Numeric and date/time searches require unformatted numbers and generic date/time strings (YYYY-MM-DD).
3. a number or a search pattern: a text string optionally containing the special characters '?' (any character) or '*' (any string). To search for a literal ? or * character, place a tilde (~) before it. Numbers and dates must be used in the unformatted form.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

NOTE: If some 'criteria' can contain pattern characters (*?~) but you don't want to perform pattern searches, use the '&' operator to prefix that criteria with '='. For example: "=" & CustomerID

NOTE: Unlike the quartile1_ex() function, the quartile1Ifs_ex() function always performs slower sequential searching. If both the source and target tables contain hundreds of thousands or millions of records at the same time, it might be too slow. You can improve its performance by including the leading "=" before each criteria (to avoid pattern matching) and by increasing the number of used processor cores in the Settings > Options dialog box.

=quartile1Ifs_ex("orders", "Total", "id", CustomerID, -1)

Returns the 1st quartile of the sales amounts for a given customer. 'orders' is a table with all orders, 'id' and 'Total' are its fields containing customers' IDs and total values for each order, respectively, and 'CustomerID' is a field of the table where the result is placed.

=quartile1Ifs_ex("folder1/orders", "Total", "id", "=" & CustomerID,)

Returns the 1st quartile of the sales amounts for a given customer; the CustomerID may contain pattern characters, but no pattern search will be performed.

=quartile1Ifs_ex("orders", "Qty", "ProductName", "Tofu", "ProductName", "Chocolade",)
=quartile1Ifs_ex("folder1/folder2/orders", "Total", "Date", ">2011-01-01", "Date", "<2011-07-01", 0)

Returns the median of the 'calcField' field values from the specified 'table' for records that meet the specified list of filters/criteria.

The 'table', 'calcField' and 'field(n)' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

References to field values/names in the same table where the formula resides must be used without any quotation marks.

The criteria can be one of the following:

1. a name of the field in the current table, optionally combined with the =, >, >=, <, <= operators.
2. a text string beginning with the =, >, >=, <, <= operators. Numeric and date/time searches require unformatted numbers and generic date/time strings (YYYY-MM-DD).
3. a number or a search pattern: a text string optionally containing the special characters '?' (any character) or '*' (any string). To search for a literal ? or * character, place a tilde (~) before it. Numbers and dates must be used in the unformatted form.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

NOTE: If some 'criteria' can contain pattern characters (*?~) but you don't want to perform pattern searches, use the '&' operator to prefix that criteria with '='. For example: "=" & CustomerID

NOTE: Unlike the median_ex() function, the medianIfs_ex() function always performs slower sequential searching. If both the source and target tables contain hundreds of thousands or millions of records at the same time, it might be too slow. You can improve its performance by including the leading "=" before each criteria (to avoid pattern matching) and by increasing the number of used processor cores in the Settings > Options dialog box.

=medianIfs_ex("orders", "Total", "id", CustomerID, 0)

Returns the median of the sales amounts for a given customer. 'orders' is a table with all orders, 'id' and 'Total' are its fields containing customers' IDs and total values for each order, respectively, and 'CustomerID' is a field of the table where the result is placed.

=medianIfs_ex("folder1/orders", "Total", "id", "=" & CustomerID,)

Returns the median of the sales amounts for a given customer; the CustomerID may contain pattern characters, but no pattern search will be performed.

=medianIfs_ex("orders", "Qty", "ProductName", "Tofu", "ProductName", "Chocolade",)
=medianIfs_ex("folder1/folder2/orders", "Total", "Date", ">2011-01-01", "Date", "<2011-07-01",)

Returns the third quartile of the 'calcField' field values from the specified 'table' for records that meet the specified list of filters/criteria.

The 'table', 'calcField' and 'field(n)' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

References to field values/names in the same table where the formula resides must be used without any quotation marks.

The criteria can be one of the following:

1. a name of the field in the current table, optionally combined with the =, >, >=, <, <= operators.
2. a text string beginning with the =, >, >=, <, <= operators. Numeric and date/time searches require unformatted numbers and generic date/time strings (YYYY-MM-DD).
3. a number or a search pattern: a text string optionally containing the special characters '?' (any character) or '*' (any string). To search for a literal ? or * character, place a tilde (~) before it. Numbers and dates must be used in the unformatted form.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

NOTE: If some 'criteria' can contain pattern characters (*?~) but you don't want to perform pattern searches, use the '&' operator to prefix that criteria with '='. For example: "=" & CustomerID

NOTE: Unlike the quartile3_ex() function, the quartile3Ifs_ex() function always performs slower sequential searching. If both the source and target tables contain hundreds of thousands or millions of records at the same time, it might be too slow. You can improve its performance by including the leading "=" before each criteria (to avoid pattern matching) and by increasing the number of used processor cores in the Settings > Options dialog box.

=quartile3Ifs_ex("orders", "Total", "id", CustomerID, 0)

Returns the 3rd quartile of the sales amounts for a given customer. 'orders' is a table with all orders, 'id' and 'Total' are its fields containing customers' IDs and total values for each order, respectively, and 'CustomerID' is a field of the table where the result is placed.

=quartile3Ifs_ex("folder1/orders", "Total", "id", "=" & CustomerID,)

Returns the 3rd quartile of the sales amounts for a given customer; the CustomerID may contain pattern characters, but no pattern search will be performed.

=quartile3Ifs_ex("orders", "Qty", "ProductName", "Tofu", "ProductName", "Chocolade",)
=quartile3Ifs_ex("folder1/folder2/orders", "Total", "Date", ">2011-01-01", "Date", "<2011-07-01",)

Returns the most frequently occurring value of the 'calcField' field values from the specified 'table' for records that meet the specified list of filters/criteria.

The 'table', 'calcField' and 'field(n)' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

References to field values/names in the same table where the formula resides must be used without any quotation marks.

The criteria can be one of the following:

1. a name of the field in the current table, optionally combined with the =, >, >=, <, <= operators.
2. a text string beginning with the =, >, >=, <, <= operators. Numeric and date/time searches require unformatted numbers and generic date/time strings (YYYY-MM-DD).
3. a number or a search pattern: a text string optionally containing the special characters '?' (any character) or '*' (any string). To search for a literal ? or * character, place a tilde (~) before it. Numbers and dates must be used in the unformatted form.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

NOTE: If some 'criteria' can contain pattern characters (*?~) but you don't want to perform pattern searches, use the '&' operator to prefix that criteria with '='. For example: "=" & CustomerID

NOTE: Unlike the mode_ex() function, the modeIfs_ex() function always performs slower sequential searching. If both the source and target tables contain hundreds of thousands or millions of records at the same time, it might be too slow. You can improve its performance by including the leading "=" before each criteria (to avoid pattern matching) and by increasing the number of used processor cores in the Settings > Options dialog box.

=modeIfs_ex("orders", "Total", "id", CustomerID, 1)

Returns the most frequently occurring sale amount for a given customer. 'orders' is a table with all orders, 'id' and 'Total' are its fields containing customers' IDs and total values for each order, respectively, and 'CustomerID' is a field of the table where the result is placed.

=modeIfs_ex("folder1/orders", "Total", "id", "=" & CustomerID, -1)

Returns the most frequently occurring sale amount for a given customer; the CustomerID may contain pattern characters, but no pattern search will be performed.

=modeIfs_ex("orders", "Qty", "ProductName", "Tofu", "ProductName", "Chocolade", 0)
=modeIfs_ex("folder1/folder2/orders", "Total", "Date", ">2011-01-01", "Date", "<2011-07-01", 0)

Counts records in the specified 'table' that meet the specified list of filters/criteria.

The 'table' and 'field(n)' parameters must be placed in quotation marks. If 'table' is located in a nested folder, its full path must be specified with the inner slashes.

References to field values/names in the same table where the formula resides must be used without any quotation marks.

The criteria can be one of the following:

1. a name of the field in the current table, optionally combined with the =, >, >=, <, <= operators.
2. a text string beginning with the =, >, >=, <, <= operators. Numeric and date/time searches require unformatted numbers and generic date/time strings (YYYY-MM-DD).
3. a number or a search pattern: a text string optionally containing the special characters '?' (any character) or '*' (any string). To search for a literal ? or * character, place a tilde (~) before it. Numbers and dates must be used in the unformatted form.

The 'emptyValue' argument specifies what should be returned (a number or a text string) if no 'v' values are found. If it's left empty, the function returns the #NULL! error code.

NOTE: If some 'criteria' can contain pattern characters (*?~) but you don't want to perform pattern searches, use the '&' operator to prefix that criteria with '='. For example: "=" & CustomerID

NOTE: Unlike the count_ex() function, the countIfs_ex() function always performs slower sequential searching. If both the source and target tables contain hundreds of thousands or millions of records at the same time, it might be too slow. You can improve its performance by including the leading "=" before each criteria (to avoid pattern matching) and by increasing the number of used processor cores in the Settings > Options dialog box.

=countIfs_ex("orders", "id", CustomerID, 0)

Returns the number of orders for a given customer. 'orders' is a table with all orders, 'id' and 'Total' are its fields containing customers' IDs and total values for each order, respectively, and 'CustomerID' is a field of the table where the result is placed.

=countIfs_ex("folder1/orders", "id", "=" & CustomerID, 0)

Returns the number of orders for a given customer; the CustomerID may contain pattern characters, but no pattern search will be performed.

=countIfs_ex("orders", "ProductName", "Tofu", "ProductName", "Chocolade", 0)
=countIfs_ex("folder1/folder2/orders", "Total", "Date", ">2011-01-01", "Date", "<2011-07-01", 0)

Searches the 'searchField' field of the specified table for the 'v' value and, if found, returns the 'returnField' value from the found record. Except for the 'v' parameter, all other parameters must be placed in quotation marks. If 'table' is located in nested folders, its full path must be specified with slashes.

The 'type' argument specifies how the searching procedure should be performed:

• 0 - vLookUp_ex will search for an exact match; case-insensitive.
• 1 - if an exact match is not found, vLookUp_ex will search for the largest value that is not greater than 'v'.
• -1 - if an exact match is not found, vLookUp_ex will search for the smallest value that is not smaller than 'v'.
• 2 - vLookUp_ex will search for an exact match; 'v' can be a search pattern containing '?' (any single character) and '*' (any string). To search for a literal '?' or '*' character, place a tilde (~) before it.
• 4 - vLookUp_ex will search for an exact match; 'v' can be a regular expression. Add 8 to perform case-sensitive searching; add 16 to allow empty matches.

NOTE: compared fields must be of the same type (either both text or numeric).

If 'type' is omitted, it's assumed to be 0.

If the match is not found, the function returns the #N/A! error value.

Fastest binary searches are performed for type=0, type=1 and type=-1.

=vLookUp_ex("products", ProductID, "ProductID", "ProductName",)
=vLookUp_ex("folder1/products", ProductID, "ProductID", "UnitPrice",)