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.

Argument Name	Description
column	Reference to the column to get the sum

Formula Examples

/* Returns the sum of the column whose name is "Amount" */
SUM([#Amount])

/* Returns the sum of the "Amount" column in the sheet with sheet ID "s1" */
SUM([s1].[#Amount])

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.

Argument Name	Description
column	Reference to the column to get the sum
match_column1	Reference to the column to make condition comparisons
match_condition1	Value to be used for conditional comparison. Accepts comparison operators.
...	Hereafter, match_column and match_condition pairs are repeated (optional)

Formula Examples

/* Returns the sum of the "Amount" column for records where the "Type" column value is "New Business" */
SUMIFS([#Amount], [#Type], "New Business")

/* Returns the sum of the values of "Amount" column where the value is greater than 0 */
SUMIFS([#Amount], [#Amount], ">0")

COUNT(column)

Returns the number of values contained in a given column. Excludes blanks from the count.

Argument Name	Description
column	Reference to the column to get the count

Formula Examples

/* Returns the number of non-blank values in the column whose name is "AccountId" */
COUNT([#AccountId])

/* Returns the number of non-blank values in the "Name" column of the sheet with sheet ID "s1" */
COUNT([s1].[#Name])

COUNTIFS(match_column1, match_condition1, ...)

Returns the number of records that match given conditions. Unlike COUNT(), it does not exclude blanks from the count by default.

Argument Name	Description
match_column1	Reference to the column to make condition comparison
match_condition1	Value to be used for conditional comparison. Accepts comparison operators.
...	Hereafter, match_column and match_condition pairs are repeated (optional)

Formula Examples

/* Returns the count of records where the "Type" column value is equal to
   the value of the current record */
COUNTIFS([#Type], [@Type])

/* Returns the count of records in sheet "s1" where " Amount" column value is 0
   and "IsWon" column value is TRUE. */
COUNTIFS([s1].[#Amount], 0, [s1].[#IsWon], TRUE)

COUNTDISTINCT(column)

Returns the number of values contained in the first argument column, excluding duplicates. Excludes blanks from the count.

Argument Name	Description
column	Reference to the column to get the count

Formula Examples

/* Returns the count of values in the column whose name is "Status", excluding duplicates */
COUNTDISTINCT([#Status])

/* Returns the count of values in the "Email" column of the sheet with sheet ID "s1",
   excluding duplicates */
COUNTDISTINCT([s1].[#Email])

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.

Argument Name	Description
column	Reference to the column to get the count
match_column1	Reference to the column to make condition comparison
match_condition1	Value to be used for conditional comparison. Accepts comparison operators.
...	Hereafter, match_column and match_condition pairs are repeated (optional)

Formula Examples

/* Returns the count of "Email" column values ending with "@example.com",
   excluding duplicates */
COUNTDISTINCTIFS([#Email], [#Email], "=*@example.com")

/* Returns the count of "Name" column values in sheet "s1", excluding duplicate,
   where "Email" column is blank */
COUNTDISTINCTIFS([s1].[#Name], [s1].[#Email], NULL)

MAXVAL(column)

Returns the maximum number of values contained in a given column.

Argument Name	Description
column	Reference to the column to get the maximum value

Formula Examples

/* Returns the maximum value in the column whose name is "NumOfEmployees */
MAXVAL([#NumOfEmployees])

/* Returns the maximum value of the "CloseDate" column in the sheet with sheet ID "s1" */
MAXVAL([s1].[#CloseDate])

MAXVALIFS(column, match_column1, match_condition1, ...)

Returns the maximum number of values contained in the first argument column for records that match given conditions.

Argument Name	Description
column	Reference to the column to get the maximum value
match_column1	Reference to the column to make condition comparison
match_condition1	Value to be used for conditional comparison. Accepts comparison operators.
...	Hereafter, match_column and match_condition pairs are repeated (optional)

Formula Examples

/* Returns the maximum value of the "Account" column value within the records
   where the "AccountId" column value equals the value of the current record */
MAXVALIFS([#Amount], [#AccountId], [@AccountId])

/* Returns the maximum value of the "Name" column within the records in sheet "s1"
   where "CloseDate" column value is later than the "LastActivityDate" column value
   of the current record */
MAXVALIFS([s1].[#Name], [s1].[#CloseDate], ">" & TEXT([@LastActivityDate]))

MINVAL(column)

Returns the minimum value of the values contained in a given column.

Argument Name	Description
column	Reference to the column to get the minimum value

Formula Examples

/* Returns the minimum value of the column whose name is "NumOfEmployees" */
MINVAL([#NumOfEmployees])

/* Returns the minimum value of the "CloseDate" column in the sheet with sheet ID "s1" */
MINVAL([s1].[#CloseDate])

MINVALIFS(column, match_column1, match_condition1, ...)

Returns the minimum number of values contained in the first argument column for records that match given conditions.

Argument Name	Description
column	Reference to the column to get the minimum value
match_column1	Reference to the column to make condition comparison
match_condition1	Value to be used for conditional comparison. Accepts comparison operators.
...	Hereafter, match_column and match_condition pairs are repeated (optional)

Formula Examples

/* Returns the minimum value of the "ActivityDate" column within records
   where the "Status" column value is "New" */
MINVALIFS([#ActivityDate], [#Status], "New")

/* Returns the minimum value of the "Amount" column within the records
   in sheet "s1" where the "CloseDate" column is blank. */
MINVALIFS([s1].[#Amount], [s1].[#CloseDate], NULL)

AVERAGE(column)

Returns the average value of the values contained in a given column.

Argument Name	Description
column	Reference to the column to get the average value

Formula Examples

/* Returns the average of the column whose name is "NumOfEmployees" */
AVERAGE([#NumOfEmployees])

/* Returns the average of the "Amount" column of the sheet with sheet ID "s1" */
AVERAGE([s1].[#Amount])

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.

Argument Name	Description
column	Reference to the column to get the average value
match_column1	Reference to the column to make condition comparison
match_condition1	Value to be used for conditional comparison. Accepts comparison operators.
...	Hereafter, match_column and match_condition pairs are repeated (optional)

Formula Examples

/* Returns the average of the "Quantity" column within the records
   where the "IsWon" column value is TRUE */
AVERAGEIFS([#Quantity], [#IsWon], TRUE)

/* Returns the average of the "Amount" column within the records in sheet "s1"
   where the "CloseDate" column has a value */
AVERAGEIFS([s1].[#Amount], [s1].[#CloseDate], "*")

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.

COUNTIFS([#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.