Aggregation Functions
Aggregation functions are functions that take column reference variables and return aggregated results.
Aggregation functions are extension functions added in the Mashmatrix Sheet formula.
List of Aggregation Functions
SUM(column)
Returns the sum of values contained in a given column.
Formula Examples
SUMIFS(column, match_column1, match_condition1, ...)
Returns the sum of values contained in the first argument column for records that match given conditions. It is possible to specify multiple pairs of matching column and matching condition value.
Formula Examples
COUNT(column)
Returns the number of values contained in a given column. Excludes blanks from the count.
Formula Examples
COUNTIFS(match_column1, match_column1, ...)
Returns the number of records that match given conditions. Unlike COUNT()
, it does not exclude blanks from the count by default.
Formula Examples
COUNTDISTINCT(column)
Returns the number of values contained in the first argument column, excluding duplicates. Excludes blanks from the count.
Formula Examples
COUNTDISTINCTIFS(column, match_column1, match_condition1, ...)
Returns the number of values contained in the first argument column, excluding duplicates, for records that match given conditions. Unlike COUNTDISTINCT()
, it does not exclude blanks from the count by default.
Formula Examples
MAXVAL(column)
Returns the maximum number of values contained in a given column.
Formula Examples
MAXVALIFS(column, match_column1, match_condition1, ...)
Returns the maximum number of values contained in the first argument column for records that match given conditions.
Formula Examples
MINVAL(column)
Returns the minimum value of the values contained in a given column.
Formula Examples
MINVALIFS(column, match_column1, match_condition1, ...)
Returns the minimum number of values contained in the first argument column for records that match given conditions.
Formula Examples
AVERAGE(column)
Returns the average value of the values contained in a given column.
Formula Examples
AVERAGEIFS(column, match_column1, match_condition1, ...)
Returns the average value of the values contained in the first argument column for records that match given conditions.
Formula Examples
Condition Value and Comparison Operator
In aggregation functions that accept conditions, condition values can be specified along with the corresponding columns to be compared. A condition value can be a string, number, boolean, or NULL, but string value can also be prefixed with a comparison operator.
Following types of comparison operators are available on the condition values of aggregation functions.
=
(equality). Exactly matches the following string or pattern<>
(inequality). Does not exactly match the following string or pattern<
(less than). Less than the value specified in the following string. If the value is numeric, it is compared numerically; if it is text, it is compared in dictionary order.<=
(less than or equal to). Less than or equal to the value specified in the following string. If the value is numeric, it is compared numerically; if it is text, it is compared in dictionary order.>
(greater than). Greater than the value specified in the following string. If the value is numeric, it is compared numerically; if it is text, it is compared in dictionary order.>=
(greater than or equal to). Greater than or equal to the value specified in the following string. If the value is numeric, it is compared numerically; if it is text, it is compared in dictionary order.
If comparison operators are omitted, equality comparisons are made with the value passed as the condition.
Wildcards in Condition Value
If the comparison is an equality (=) or inequality (<>) comparison, it is possible to use pattern matching by using wildcards in the string of the condition value. The following types of wildcards are available.
*
(asterisk). Represents any multiple characters (including empty string).?
(question). Represents any one character.
If you want to use a character specified as a wildcard for comparison, escape it using the escape character ~
. For example, the following example counts up records that match the string *
within the values of Column01.
Aggregation Functions Limitations
Aggregate functions only target records retrieved on the sheet, not entire records stored in the Salesforce object. You can increase the number of records to be searched by changing the "Max Fetch Record Num" from the sheet settings.
Field variables cannot be set in the aggregate or condition columns in the aggregation function arguments. To aggregate a Salesforce field, you must first add the field as a column to the sheet, and then pass the added column to the aggregation function.
The aggregate or condition columns in the aggregation function arguments must be columns that belong to the same sheet.
Last updated