🧮

List of formulas supported

List of formulas and parameters

FormulaFormula descriptionSummaryExampleAvailable?
Include
INCLUDE(substring, string)
Returns TRUE if the string contains the substring, FALSE otherwise
INCLUDE("string", "is the word string in this sentence?)
Yes
Timestamp_to_date
TIMESTAMPTODATE(timestamp)
Returns a date with the format "MM-DD-YYYY". Takes a UNIX timestamp (in seconds) as argument.
TIMESTAMPTODATE(308966400)
Yes
Year
YEAR(DATE)
Returns the year of a specific date
YEAR("10/17/1979")
Yes
Month
MONTH(DATE)
Returns the month of a specific date, in numeric format
MONTH("10/17/1979")
Yes
Iferror
IFERROR(value, [value_if_error])
Returns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.
IFERROR(A1/A2, "Error")
Yes
Ceiling
CEILING(value, factor)
Rounds a number down. Factor is optional.
FLOOR(23.25, 0.1) will return 23.3 FLOOR(23.25, 1) will return 24
Yes
Floor
FLOOR(value, factor)
Rounds a number down. Factor is optional.
FLOOR(23.25, 0.1) will return 23.2 FLOOR(23.25, 1) will return 23
Yes
Weeknum
WEEKNUM(DATE)
Returns the week number
WEEKNUM("10/17/1979")
Yes
Average
AVERAGE(#column_name)
Returns the average value of a dataset
AVERAGE(#selling_price)
Yes
Date
DATE(year, month, day)
Converts a provided year, month, and day into a date.
DATE(1979, 10, 17)
Yes
Round
ROUND(value, [places])
Rounds a number to a certain number of decimal places, always rounding down to the next valid increment.
ROUND(99.44, 1)
Yes
Roundup
ROUNDUP(value, [places])
Rounds a number to a certain number of decimal places, always rounding up to the next valid increment.
ROUNDUP(99.44, 1)
Yes
Rounddown
ROUNDDOWN(value, [places])
Rounds a number to a certain number of decimal places according to standard rules.
ROUNDDOWN(99.44, 1)
Yes
PMT
PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])
Calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate.
PMT(1%, 360, 100000, 0, 0)
Yes
Not
NOT(logical_expression)
Returns the opposite of a logical value - NOT(TRUE) returns FALSE; NOT(FALSE) returns TRUE.
NOT(TRUE)
Yes
Int
INT(value)
Rounds a number down to the nearest integer that is less than or equal to it.
INT(24.8)
Yes
And
AND(logical_expression1, [logical_expression2, …])
Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.
AND(A1=1, A2=2)
Yes
Or
OR(logical_expression1, [logical_expression2, …])
Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false.
OR(A1=1, A2=2)
Yes
If
IF(logical_expression, value_if_true, value_if_false)
Returns one value if a logical expression is TRUE and another if it is FALSE.
IF(42>9, “all good”, “something is wrong in the matrix”)
Yes
Min
MIN(value1, [value2, …])
Returns the minimum value in a numeric dataset.
MIN(A3, 5)
Yes
Max
MAX(value1, [value2, …])
Returns the maximum value in a numeric dataset.
MAX(A3, 42)
Yes
Networkdays
NETWORKDAYS(start_date, end_date, [holidays])
Returns the number of net working days between two provided dates.
NETWORKDAYS(A1, B1, C1:C10)
Yes
True
TRUE()
Returns the logical value TRUE
TRUE()
Yes
False
FALSE()
Returns the logical value FALSE
FALSE()
Yes
Today
TODAY()
Returns the current date.
TODAY()
Yes
Sum
SUM(value1, [value2, …]) or SUM(#column_name)
Returns the sum of a series of numbers and/or cells or of a column
SUM(A2:A100, 101) or SUM(#price)
Yes
Lookup
LOOKUP(#column_id_current_table, #column_id_second_table, #column_to_lookup)
Searches down a column for a key and returns the value of a specified cell in the row found. Cannot be nested in another function. Nor can a function be nested in a lookup
LOOKUP(#customerid, ‘Sheet2!’#id, ‘Sheet2!’#name)
Yes
Countuniqueifs
COUNTUNIQUEIFS(#column_name, #criteria_column1, criterion1, [#criteria_column2, …], [criterion2, …])
Counts the number of unique values in a column, filtered by a set of criteria applied to additional columns.
COUNTUNIQUEIFS(#user_name, #country, "France", #signup_date, ">"&E3)
Yes
Countunique
COUNTUNIQUE(#column_name)
Counts the number of unique values in a column.
COUNTUNIQUE(#user_name)
Yes
Countif
COUNTIF(#criteria_column, criterion)
Returns the count of a column depending on one criteria.
COUNTIF(#product_category, "Phones")
Yes
Sumif
SUMIF(#sum_column, #criteria_column, criterion)
Returns the sum of a column depending on one criteria.
SUMIF(#price, #product_category, "Phones")
Yes
Averageifs
AVERAGEIFS(#average_column, #criteria_column, criterion, ...)
Returns the average of a column depending on multiple criteria.
AVERAGEIFS(#selling_price, #product_category, "phones", #country, "France")
Yes
Counta
COUNTA(#column_name)
Returns the number of values in a column
COUNTA(#booking_id)
Yes
Sumifs
SUMIFS(#sum_column, #criteria_column1, criterion1, [#criteria_column2, …], [criterion2, …])
Returns the sum of a column depending on multiple criteria.
SUMIFS(#price, #product_category, "Phones", #booking_date, ">"&E3)
Yes
Countifs
COUNTIFS(#criteria_column1, criterion1, [#criteria_column2, …], [criterion2, …])
Returns the count of a column depending on multiple criteria.
COUNTIFS(#product_category, "Phones", #booking_date, ">"&E3)
Yes