Aggregate functions perform an analysis and/or calculation on source data (multiple rows) and return an aggregate value based on that data.
Returns the average of numeric values in the given cell range or data array. You may specific multiple ranges.
This function is useful for calculating overall trends like average revenue, costs, or headcount across selected data sets.
AVERAGE(data, data, ..)
Examples:
AVERAGE(A2:A9)
AVERAGE(A2:A9, A20:A22)
Returns the average of non-zero numeric values in the given cell range or data array. You may specific multiple ranges. The difference with AVERAGE is that zero values are exlcuded from the average calculation.
This function is useful for avoiding distortion in averages caused by zero values, which is ideal when measuring performance only from active data points.
AVERAGEA(data, data, ..)
Examples:
AVERAGEA(A2:A9)
AVERAGEA(A2:A9, A20:A22)
Returns the average of numeric values in the given cell range or data array that meet the given condition. The condition can either be a single value if you just want to compare against that value, or an expression with an operator and value, within quotes (e.g. ">10").
The data parameter is the range of which you want to calculate the average value of items that meet the condition. If you want to calculate the average of a range other than data, you can optionally provide the range parameter. You can omit range if it is the same as the data range.
This function is useful for calculating conditional KPIs, such as average sales above a threshold or average expense per active project.
AVERAGEIF(data, condition, range)
Example:
AVERAGEIF(A2:A9, ">10", B2:B9)
Returns the number of numeric values in the given cell range or data array. You may specific multiple ranges.
This function is useful for quantifying how many valid (numeric) entries exist, e.g. number of expense lines or transactions.
COUNT(data, data, ..)
Examples:
COUNT(A2:A9)
COUNT(A2:A9, A20:A22)
Returns the number of non-blank items in the given cell range or data array. You may specific multiple ranges.
This function is useful for validating completeness of records, such as filled-out forms or responses in surveys.
COUNTA(data, data, ..)
Examples:
COUNTA(A2:A9)
COUNTA(A2:A9, A20:A22)
Returns the number of items in the given cell range or data array that are between the given from and to parameters.
This function is useful for checking how many values lie within a range, such as mid-tier customers, or invoices between €100 and €500.
COUNTBETWEEN(data, from, to)
Example:
COUNTBETWEEN(A2:A9, 100, 200)
Returns the number of blank items in the given cell range or data array. You may specific multiple ranges.
This function is useful for auditing missing entries or identifying incomplete submissions or data gaps.
COUNTBLANK(data, data, ..)
Examples:
COUNTBLANK(A2:A9)
COUNTBLANK(A2:A9, A20:A22)
Returns the number of distinct (= unique) items in the given cell range or data array. This function works as follows:
This function is useful for deduplicating lists, identifying unique customers or accounts, and analyzing data diversity.
COUNTDISTINCT(data)
Example:
COUNTDISTINCT(A2:A9)
Returns the number of items in the given cell range or data array that meet the given condition. The condition can either be a single value if you just want to compare against that value, or an expression with an operator and value, within quotes (e.g. ">10").
This function is useful for tallying qualified data points, such as overdue invoices or above-budget costs.
COUNTIF(data, condition)
Example:
COUNTIF(A2:A9, ">10")
Returns the number of underlying rows in the given cell range or data array. This function works as follows:
This function is useful for aggregating row-level transactions or validating report data volume in models, reports, and imports.
COUNTROWS(data)
Example:
COUNTROWS(A2:A9)
Returns the number of words in the given text, cell range, or data array. If you want to count the number of times a certain word occurs, you can optionally provide the word parameter (this is case-insensitive). The recognised separators between words are a space and the following characters . , ; : = ! ?
This function is useful for scanning comments or notes for word frequency (e.g. complaints, product mentions), or simply counting words for documentation.
COUNTWORD(data, word)
Examples:
data = "The fox jumps over the fence"
COUNTWORD(data) returns
6
COUNTWORD(data, "the") = 2
COUNTWORD(A1:A3) = 6
Returns the first (i.e. earliest) date from the given cell range or data array. Items that are not valid dates are ignored. You can specifiy multiple ranges.
This function is useful for determining the start date of a project, earliest transaction, or onboarding time.
FIRSTDATE(data, data, ..)
Examples:
FIRSTDATE(A2:A9)
FIRSTDATE(A2:A9, A20:A22)
Groups all data in a data set based on the specified number of left-most characters in the field. This function will aggregate the detail data based on the grouped value, which may improve the speed of generating reports that only need to show aggregated data, rather than all underlying data. This function is only relevant in reports and queries in models.
This function is useful for improving report performance by aggregating large data sets into simplified groups (e.g. by month or year).
GROUPBY(data, type)
type:
1-9 = number of left-most characters
"d" = dates (YYYY-MM-DD or 10 characters)
"p" = periods (YYYY-MM or 7 characters)
"y" = years (YYYY or 10 characters)
Examples:
data = "2019-06-30"
GROUPBY(data, "p") = "2019-06"
GROUPBY(data,
7) = "2019-06"
Returns the last (i.e. latest) date from the given cell range or data array. Items that are not valid dates are ignored. You can specifiy multiple ranges.
This function is useful for identifying most recent activity, such as last login, transaction, or update timestamp.
LASTDATE(data, data, ..)
Examples:
LASTDATE(A2:A9)
LASTDATE(A2:A9, A20:A22)
Returns the maximum value from the given cell range or data array. The values can be numeric or texts. For dates, you should use the LASTDATE function. You can specifiy multiple ranges.
This function is useful for highlighting peak values such as highest revenue, largest order, or top spending account.
MAX(data, data, ..)
Examples:
MAX(A2:A9)
MAX(A2:A9, A20:A22)
Returns the minimum value from the given cell range or data array. The values can be numeric or texts. For dates, you should use the FIRSTDATE function. You can specifiy multiple ranges.
This function is useful for finding the lowest metric like smallest expense, minimum stock level, or bottom performer.
MIN(data, data, ..)
Examples:
MIN(A2:A9)
MIN(A2:A9, A20:A22)
Returns the product of all numeric values (i.e. multiplying all values) in the given cell range(s) or data array(s). You can provide multiple values. You can specifiy multiple ranges.
This function is useful for calculations like compound growth, scaling factors, or investment multipliers across data sets.
PRODUCT(data, data, ..)
Examples:
PRODUCT(A2:A9)
PRODUCT(A2:A9, A20:A22)
Returns the subtotal in the given cell range, by counting only the rows that meet the current filter criteria. This function is only applicable in models in combination with cell filters, and is only meant for columns of data (vertical ranges), not for rows of data (horizontal ranges).
You can only use one single cell range, and the rows in this range needs to correspond to the rows in the range defined in cell filters. For example, if you have a filter set in cell A1 that covers range A2:A9 you can use SUBTOTAL(9, A2:A9) to get the SUM of the amounts in all rows within that range that have been filtered. If the cell range in SUBTOTAL differs from the cell range of cell filters, the result may not be correct.
This function is useful for interactive analysis where you want totals that respect applied filters, which is great for building dynamic dashboards.
SUBTOTAL(type, data)
type:
1 = AVERAGE
2 = COUNT
3 = COUNTA
4 = MAX
5 = MIN
6
= PRODUCT
7 = STDEVP
8 = STDEV
9 = SUM
10 = VARP
11 = VAR
Example:
SUBTOTAL(9, A2:A9)
Returns the sum of numeric values in the given cell range or data array. You may specific multiple ranges.
This function is useful for adding totals such as revenues, costs, or hours worked. It’s the foundation for financial calculations.
SUM(data, data, ..)
Examples:
SUM(A2:A9)
SUM(A2:A9, A20:A22)
Returns the sum of numeric values of items in the given cell range or data array where the given field is within the given from and to parameters.
data is the range of which you want to calculate the number of items that meet the condition. If you want to calculate the number of items in a range other than data, you can optionally provide the range parameter. You can omit range if it is the same as the data range.
This function is useful for summing transactions in a specific time range, like a quarter or fiscal year window.
SUMBETWEEN(data, from, to, range)
Example:
SUMBETWEEN(A2:A9, "2019-02", "2019-03", B2:B9)
Returns the sum of numeric values in the given cell range or data array that meet the given condition. The condition can either be a single value if you just want to compare against that value, or an expression with an operator and value, within quotes (e.g. ">10").
The data parameter is the range of which you want to calculate the sum of items that meet the condition. If you want to calculate the sum of a range other than data, you can optionally provide the range parameter. You can omit range if it is the same as the data range.
This function is useful for aggregating filtered data like total expenses over €500 or sales per region.
SUMIF(data, condition, range)
Example:
SUMIF(A2:A9, "2019-02", B2:B9)
Returns the sum of the product of numeric values (i.e. multiplying all values) on every row in the given cell range(s) or data array(s). You can provide multiple ranges, in which case all cells on the same row across all ranges will be multiplied. You can specifiy multiple ranges.
This function is useful for multiplying and summing unit-price and quantity fields, useful in sales or cost modeling.
SUMPRODUCT(data, data, ..)
Example:
SUMPRODUCT(B2:B9, C2:C9)
Returns the sum of the product of numeric values (i.e. multiplying all values) on every row in the given cell range or data array(s) that meet the given condition. You can provide multiple values, in which case all cells on the same row across all ranges will be multiplied. The condition can either be a single value if you just want to compare against that value, or an expression with an operator and value, within quotes (e.g. ">10").
This function is useful for calculating weighted totals based on criteria, like sales per product group or time period.
SUMPRODUCTIF(data, condition, range)
Example:
SUMPRODUCTIF(A2:A9, "2019-02", B2:C9, C2:C9)
Returns the sum of the squares of numeric values (i.e. multiplying each value by itself) in the given cell range(s) or data array(s). You may specify multiple values.
This function is useful for performing variance or standard deviation calculations in data modeling and error tracking.
SUMSQ(data, data, ..)
Examples:
SUMSQ(A2:A9)
SUMSQ(A2:A9, A20:A22)