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 k^th percentile value of a column or group.

PercentileDisc

Calculates the discrete k^th 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.