Functions for aggregate

Aggregate functions perform an analysis and/or calculation on source data (multiple rows) and return an aggregate value based on that data.

AVERAGE

Returns the average of numeric values in the given cell range or data array.

AVERAGE(data)

Example:
AVERAGE(A2:A9)

AVERAGEIF

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

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

COUNT

Returns the number of items in the given cell range or data array.

COUNT(data)

Example:
COUNT(A2:A9)

COUNTA

Returns the number of non-blank values in the given cell range or data array.

COUNTA(data)

Example:
COUNTA(A2:A9)

COUNTBETWEEN

Returns the number of items in the given cell range or data array that are between the given "from" and "to" values.

COUNTBETWEEN(data, from, to)

Example:
COUNTBETWEEN(A2:A9, 100, 200)

COUNTBLANK

Returns the number of blank items in the given cell range or data array.

COUNTBLANK(data)

Example:
COUNTBLANK(A2:A9)

COUNTDISTINCT

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)

COUNTIF

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

COUNTROWS

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)

FIRSTDATE

Returns the first (i.e. earliest) date from the given cell range or data array. Items that are not valid dates are ignored.

FIRSTDATE(data)

Example:
FIRSTDATE(A2:A9)

LASTDATE

Returns the last (i.e. latest) date from the given cell range or data array. Items that are not valid dates are ignored.

LASTDATE(data)

Example:
LASTDATE(A2:A9)

MAX

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.

MAX(data)

Example:
MAX(A2:A9)

MIN

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.

MIN(data)

Example:
MIN(A2:A9)

PRODUCT

Returns the product of numeric values in the given cell range or data array.

PRODUCT(data)

Example:
PRODUCT(A2:A9)

SUM

Returns the sum of numeric values in the given cell range or data array.

SUM(data)

Example:
SUM(A2:A9)

SUMBETWEEN

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

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

SUMIF

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

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

SUMPRODUCT

Returns the sum of the product of corresponding numeric values in 2 or more given cell ranges or data arrays.

SUMPRODUCT(data, range)

Example:
SUMPRODUCT(A2:A9, B2:B9)

SUMSQ

Returns the sum of the squares of numeric values in the given cell range or data array.

SUMSQ(data)

Example:
SUMSQ(A2:A9)