Aggregate functions overview
Aggregate functions process and summarize the values of multiple rows into a single computational result. You can use aggregate functions on grouped levels, or in a total column. Add all values of a column using the Sum function, or Count the number of values for each grouping in a column. Aggregates must always refer to columns at a lower level than the desired result.
When you group the aggregated column, Sigma calculates the aggregate for each group. For example, if the worksheet is grouped by the column [Year], then the function Sum([Revenue]) computes the revenue for each year.
You can reference the results of aggregate functions from lower levels. For example, create a Sum([Amount]) column as Total, and an [Amount] / [Total] column as Percentage. This converts each Amount value into a relative percentage value.
- Avg
- Determines the average value of a column or group.
- AvgIf
- Determines the average value for a column or group where the specified conditions are
TRUE. - Corr
- Calculates the Pearson correlation coefficient (bivariate correlation) of two columns.
- Count
- Calculates the number of non-null and non-empty values in a column or group.
- CountDistinct
- Calculates the number of unique (not repeating) values in a column or group; same as Ndv.
- CountDistinctIf
- Calculates the number of unique (not repeating) values in a column or group where the specified conditions are
TRUE. - CountIf
- Calculates the number of values in a column or group where the specified conditions are
TRUE. - GrandTotal
- Calculates the grand total for a grouped aggregation column.
- Max
- Determines the maximum value for a column or group.
- MaxIf
- Determines the maximum value for a column or group where the specified conditions are
TRUE. - Median
- Determines the median value of a column or group; the midpoint value in a probability distribution.
- Min
- Determines the minimum value for a column or group.
- MinIf
- Determines the minimum value for a column or group where the specified conditions are
TRUE. - Ndv
- Calculates the number of unique (not repeating) values in a column or group; same as CountDistinct.
- PercentileCont
- Calculates the continuous kth percentile value of a column or group.
- PercentileDisc
- Calculates the discrete kth percentile value of a column or group.
- PercentOfTotal
- Calculates the percent total for an aggregate formula.
- StdDev
- Calculates the standard deviation of a column or group.
- Subtotal
- Calculates the subtotal of a column or group.
- Sum
- Calculates the sum of a column or group.
- SumIf
- Calculates the sum of a column or group where the specified conditions are
TRUE. - SumProduct
- Calculates the sum of the product of values in a series of columns.
- Variance
- Calculates the statistical variance of a column or group.
The following Window functions also operate on aggregates:
- ListAgg
- Aggregates a column's row values into a single list, for each window; returns a text column.
- ListAggDistinct
- Aggregates a column's distinct (non-duplicate) row values into a single list, for each window; returns a text column.