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.
AVERAGE(data, data, ..)
Example:
AVERAGE(A2:A9)
AVERAGE(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.
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.
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.
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:
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:
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.
FIRSTDATE(data, data, ..)
Example:
FIRSTDATE(A2:A9)
FIRSTDATE(A2:A9, A20:A22)
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, 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.
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.
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.
PRODUCT(data, data, ..)
Example:
PRODUCT(A2:A9)
PRODUCT(A2:A9, A20:A22)
Returns the sum of numeric values in the given cell range or data array.
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.
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 can provide multiple values.
SUMSQ(data, data, ..)
Example:
SUMSQ(A2:A9)
SUMSQ(A2:A9, A20:A22)