In this article:

All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special

All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special

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.

AVERAGE(data, data, ..)

Example:

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.

AVERAGEA(data, data, ..)

Example:

AVERAGEA(A2:A9)

AVERAGEA(A2:A9, A20:A22)

Returns the average of numeric values 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").

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.

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.

COUNT(data, data, ..)

Example:

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.

COUNTA(data, data, ..)

Example:

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.

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.

COUNTBLANK(data, data, ..)

Example:

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:

- When used within a model, it will count the number of distinct items in the given cell range.
- When used in a report, it will count the number of distinct items in the underlying report
data. You can place the field in the
**Values**section to sum the distinct count at every level in your report. Please note that the level of aggregation (i.e. the fields in the**Filters**and**Rows**section) in your report layout will impact the count. - When used in an import, it will return 1 for every row that contains a new distinct value, and 0 for any subsequent rows that contain repeated values (i.e. not distinct) data.

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").

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:

- When used within a model, it will count the number of rows in the given cell range.
- When used in a report, it will count the number of rows in the underlying report data. You
can place the field in the
**Values**section to sum the count at every level in your report. This function is counting the actual number of rows, irrespective of the aggregation in your report. - When used in an import, it will return the number of rows in the source data.

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 . , ; : = ! ?

COUNTWORD(data, word)

Example:

data = "The fox jumps over the fence"

COUNTWORD(data) returns
6

COUNTWORD(data, "the") returns 2

COUNTWORD(A1:A3) returns 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.

FIRSTDATE(data, data, ..)

Example:

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 show aggregated data, rather than all underlying data. This function is only relevant in reports and queries in models.

You can either specify the number of left-most characters, or use one of these aliases for period grouping:

- "d" for dates (YYYY-MM-DD or 10 characters)
- "p" for periods (YYYY-MM or 7 characters)
- "y" for years (YYYY or 10 characters)

GROUPBY(data, type)

Example:

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.

LASTDATE(data, data, ..)

Example:

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.

MAX(data, data, ..)

Example:

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.

MIN(data, data, ..)

Example:

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.

PRODUCT(data, data, ..)

Example:

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.

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.

SUM(data, data, ..)

Example:

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.

SUMBETWEEN(data, from, to, range)

Example:

SUMBETWEEN(A2:A9, "2019-02", "2019-03", B2:B9)

Returns the sum of numeric values 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").

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.

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.

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").

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.

SUMSQ(data, data, ..)

Example:

SUMSQ(A2:A9)

SUMSQ(A2:A9, A20:A22)