# 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**

**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**

**Formula Examples**

### COUNT(column)

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

**Formula Examples**

**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**

**Formula Examples**

### COUNTDISTINCT(column)

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

**Formula Examples**

**Formula Examples**

**COUNTDISTINCTIFS(column, match_column1, match_condition1, ...)**

**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**

**Formula Examples**

### MAXVAL(column)

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

**Formula Examples**

**Formula Examples**

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

**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**

**Formula Examples**

### MINVAL(column)

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

**Formula Examples**

**Formula Examples**

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

**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**

**Formula Examples**

### AVERAGE(column)

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

**Formula Examples**

**Formula Examples**

**AVERAGEIFS(column, match_column1, match_condition1, ...)**

**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**

**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