Overview

Use functions and operators in Sheets to analyze and transform your data. Functions are available in two contexts:

• Table calculated columns: Functions that transform or enrich individual row values in a table, applied at the column level.
• Sheets: Functions entered directly in a sheet tab, allowing you to reference other sheet or table tabs (when applicable).

Operators

Functions

Text

REGEXMATCH(text_string, regular_expression)

Evaluates whether a text string matches a regular expression.
Example: REGEXMATCH("ABC 123 def", "\\\\d+") => TRUE
Available in: Table, Sheet

REGEXEXTRACT(text_string, regular_expression)

Extracts the first substring that matches a specified regex pattern.
Example: REGEXEXTRACT("ABC 123 def", "\\\\d+") => "123"
Available in: Table, Sheet

REGEXCOUNT(text_string, regular_expression)

Counts the number of times a regex pattern appears in a text string.
Example: REGEXCOUNT("abc 123 def", "\\\\d+") => 1
Available in: Table, Sheet

REGEXREPLACE(text_string, regular_expression, replacement)

Replaces all substrings matching a regex with a replacement string.
Example: REGEXREPLACE("abc 123 def", "\\\\d+", "NUM") => "abc NUM def"
Available in: Table

LEN(string)

Returns the length of a string.
Example: LEN("Hello World")
Available in: Table, Sheet

LOWER(string)

Returns the string in lowercase.
Example: LOWER("HELLO WORLD")
Available in: Table, Sheet

UPPER(string)

Returns the string in uppercase.
Example: UPPER("hello world")
Available in: Table, Sheet

LEFT(string, number_of_characters)

Returns a substring from the beginning of a specified string.
Example: LEFT("Datadog", 4)
Available in: Table, Sheet

RIGHT(string, number_of_characters)

Returns a substring from the end of a specified string.
Example: RIGHT("DATADOG", 3)
Available in: Table, Sheet

MID(text, start, length)

Returns characters from the middle of text.
Example: MID("Hello World", 7, 5) => "World"
Available in: Sheet

CONCATENATE(string1, string2, ...)

Appends strings to one another. Equivalent to the & operator.
Example: CONCATENATE("data", "dog")
Available in: Table, Sheet

CONTAINS(string, substring)

Returns TRUE if the string contains the substring, FALSE otherwise.
Example: CONTAINS("is the word string in this sentence?", "string")
Available in: Table, Sheet

SUBSTITUTE(text, old_text, new_text, [instance_num])

Replaces occurrences of old_text with new_text. If instance_num is omitted, all occurrences are replaced; otherwise, only the specified instance is replaced.
Example: SUBSTITUTE("hello world", "world", "Datadog") => "hello Datadog"
Available in: Table, Sheet

TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])

Combines the text from multiple strings with the specified delimiter.
Example: TEXTJOIN(" ", TRUE, "hello", "world")
Available in: Table, Sheet

FIND(search_for, text_to_search)

Finds the position of text within text (case-sensitive). Returns an error if not found.
Example: FIND("World", "Hello World") => 7
Available in: Sheet

CHAR(number)

Converts a number into a character according to the Unicode character set.
Example: CHAR(65) => "A"
Available in: Sheet

CLEAN(text)

Removes non-printable characters from text.
Example: CLEAN(A1)
Available in: Sheet

TEXT(number, format)

Formats a number as text using a format pattern. Supports number, date, and time formatting.
Example: TEXT(1234.5, "#,##0.00") => "1,234.50"
Available in: Sheet

TRIM(text)

Removes leading, trailing, and extra spaces from text.
Example: TRIM(" hello ") => "hello"
Available in: Sheet

VALUE(text)

Converts text to a number.
Example: VALUE("123") => 123
Available in: Sheet

Logical

IF(logical_expression, value_if_true, value_if_false)

Returns one value if a logical expression is TRUE and another if it is FALSE.
Example: IF(42>9, "all good", "something is wrong in the matrix")
Available in: Table, Sheet

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)

Evaluates one or more condition/value pairs and returns the value for the first true condition. Use TRUE as the final condition to define a default value.
Example: IFS(A1>90, "A", A1>80, "B", TRUE, "C")
Available in: Table, Sheet

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.
Example: AND(A1=1, A2=2)
Available in: Table, Sheet

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.
Example: OR(A1=1, A2=2)
Available in: Table, Sheet

NOT(logical_expression)

Returns the opposite of a logical value.
Example: NOT(TRUE)
Available in: Table, Sheet

TRUE()

Returns the logical value TRUE.
Example: TRUE()
Available in: Table, Sheet

FALSE()

Returns the logical value FALSE.
Example: FALSE()
Available in: Table, Sheet

IFERROR(value, value_if_error)

Returns a specified value if a formula evaluates to an error; otherwise returns the result of the formula.
Example: IFERROR(1/0, "Division Error")
Available in: Sheet

IFNA(value, value_if_na)

Returns a specified value if a formula evaluates to #N/A; otherwise returns the result of the formula.
Example: IFNA(VLOOKUP("x", A1:B10, 2, FALSE), "Not found")
Available in: Sheet

SWITCH(expression, case1, value1, ..., [default])

Compares expression to cases and returns the corresponding value.
Example: SWITCH(A1, 1, "One", 2, "Two", "Other")
Available in: Sheet

XOR(logical_expression1, [logical_expression2, …])

Returns TRUE if an odd number of arguments are TRUE.
Example: XOR(TRUE, FALSE)
Available in: Sheet

Math

ABS(number)

Returns the absolute value of a number.
Example: ABS(26.34)
Available in: Table, Sheet

CEILING(number, factor)

Rounds a number up to the nearest integer multiple of the specified factor.
Example: CEILING(826.645, 10)
Available in: Table, Sheet

FLOOR(number, factor)

Rounds a number down to the nearest integer multiple of the specified factor.
Example: FLOOR(826.645, 10)
Available in: Table, Sheet

MOD(number1, number2)

Returns the result of the modulo operator, the remainder after a division operation.
Example: MOD(5, 2)
Available in: Table, Sheet

POWER(number, power)

Returns a number raised to a power.
Example: POWER(2, 3)
Available in: Table, Sheet

ROUND(number, places)

Rounds a number to a certain number of decimal places.
Example: ROUND(826.645, 1)
Available in: Table, Sheet

COUNT(value1, [value2, ...])

Counts the number of numeric values in a range.
Example: COUNT(A1:A10)
Available in: Sheet

COUNTA(value1, [value2, ...])

Counts the number of non-empty values in a range.
Example: COUNTA('Logs'#"service")
Available in: Sheet

COUNTBLANK(range)

Counts the number of empty cells in a range.
Example: COUNTBLANK(A1:A10)
Available in: Sheet

COUNTIF(range, criteria)

Counts the number of cells in a range that meet a specified criteria.
Example: COUNTIF('Logs'#"status", "error")
Available in: Sheet

COUNTIFS(range1, criteria1, [range2, criteria2, ...])

Counts the number of cells in a range that meet multiple criteria.
Example: COUNTIFS('Logs'#"status", "error", 'Logs'#"env", "prod")
Available in: Sheet

COUNTUNIQUE(value1, [value2, ...])

Counts the number of unique values in a range.
Example: COUNTUNIQUE('Logs'#"service")
Available in: Sheet

MAX(value1, [value2, ...])

Returns the largest number from a set of values.
Example: MAX('APM'#"duration")
Available in: Sheet

MAXIFS(max_range, range1, criteria1, ...)

Returns the maximum value in a range that meets multiple criteria.
Example: MAXIFS('APM'#"duration", 'APM'#"env", "prod")
Available in: Sheet

MIN(value1, [value2, ...])

Returns the smallest number from a set of values.
Example: MIN('APM'#"duration")
Available in: Sheet

MINIFS(min_range, range1, criteria1, ...)

Returns the minimum value in a range that meets multiple criteria.
Example: MINIFS('APM'#"duration", 'APM'#"env", "prod")
Available in: Sheet

PI()

Returns the value of π to 15 digits of precision.
Example: PI()
Available in: Sheet

RAND()

Returns a random number between 0 and 1.
Example: RAND()
Available in: Sheet

SQRT(number)

Returns the positive square root of a number.
Example: SQRT(16) => 4
Available in: Sheet

SUM(value1, [value2, ...])

Returns the sum of a series of numbers and/or cells.
Example: SUM('Cloud Cost'#"cost")
Available in: Sheet

SUMIF(range, criteria, sum_range)

Adds the values in a range that meet criteria you specify.
Example: SUMIF('Cloud Cost'#"service", "ec2", 'Cloud Cost'#"cost")
Available in: Sheet

SUMIFS(sum_range, range1, criteria1, ...)

Adds the values in a range that meet multiple criteria.
Example: SUMIFS('Cloud Cost'#"cost", 'Cloud Cost'#"service", "ec2", 'Cloud Cost'#"env", "prod")
Available in: Sheet

Date and time

DATE(year, month, day)

Converts a provided year, month, and day into a date.
Example: DATE(2021, 10, 31)
Available in: Table, Sheet

DATEDIF(start_date, end_date, unit)

Calculates the number of days, months, or years between two dates.
Example: DATEDIF("10/17/1979", "8/22/2019", "Y") => 39
Available in: Table, Sheet

TODAY()

Returns the current date.
Example: TODAY()
Available in: Sheet

NOW()

Returns the current date and time.
Example: NOW()
Available in: Sheet

TIME(hour, minute, second)

Converts a provided hour, minute, and second into a time.
Example: TIME(11, 40, 59)
Available in: Sheet

YEAR(date)

Extracts the year component from a date value.
Example: YEAR(DATE(2025, 12, 31))
Available in: Sheet

MONTH(date)

Extracts the month component from a date value.
Example: MONTH("2023-07-15")
Available in: Sheet

DAY(date)

Extracts the day component from a date value.
Example: DAY(DATE(2023, 12, 25))
Available in: Sheet

HOUR(time)

Extracts the hour component from a time value.
Example: HOUR("14:30:45")
Available in: Sheet

MINUTE(time)

Extracts the minute component from a time value.
Example: MINUTE("14:30:45")
Available in: Sheet

SECOND(time)

Extracts the second component from a time value.
Example: SECOND("14:30:45")
Available in: Sheet

EDATE(start_date, months)

Returns the date that is the indicated number of months before or after a start date.
Example: EDATE("2023-01-15", 6)
Available in: Sheet

EOMONTH(start_date, months)

Returns the last day of a month that is a specified number of months before or after a given date.
Example: EOMONTH(DATE(2023, 12, 12), 0)
Available in: Sheet

WEEKDAY(date, [type])

Returns the day of the week as a number. Type 1 (default) = Sun–Sat (1–7), type 2 = Mon–Sun (1–7), type 3 = Mon–Sun (0–6).
Example: WEEKDAY(DATE(2023, 12, 12))
Available in: Sheet

WEEKNUM(date, [type])

Returns the week number of a specific date within the year.
Example: WEEKNUM("2023-01-15")
Available in: Sheet

Lookup and reference

VLOOKUP(search_key, range, index, [is_sorted])

Searches for a value in the first column of a range and returns a value in the same row from a specified column.
Example: VLOOKUP("Apple", A1:C10, 2, FALSE)
Available in: Sheet

HLOOKUP(search_key, range, index, [is_sorted])

Searches for a value in the first row of a range and returns a value in the same column from a specified row.
Example: HLOOKUP("Apple", A1:D3, 2, FALSE)
Available in: Sheet

INDEX(reference, row, [column])

Returns the value of an element in a table based on row and column numbers.
Example: INDEX(A1:D3, 2, 3)
Available in: Sheet

MATCH(search_key, range, [search_type])

Returns the relative position of an item in an array that matches a specified value.
Example: MATCH("Apple", A1:A4, 0)
Available in: Sheet

CHOOSE(index, value1, value2, ...)

Returns a value from a list based on an index.
Example: CHOOSE(2, "A", "B", "C")
Available in: Sheet

ROW([reference])

Returns the row number of a reference.
Example: ROW(A5) => 5
Available in: Sheet

COLUMN([reference])

Returns the column number of a reference.
Example: COLUMN(C1) => 3
Available in: Sheet

Statistical

AVERAGE(value1, [value2, ...])

Returns the numerical average value in a dataset, ignoring text.
Example: AVERAGE('APM'#"duration")
Available in: Sheet

AVERAGEIF(range, criteria, [average_range])

Returns the average of cells that meet a specified criteria.
Example: AVERAGEIF('APM'#"env", "prod", 'APM'#"duration")
Available in: Sheet

AVERAGEIFS(average_range, range1, criteria1, ...)

Returns the average of cells that meet multiple criteria.
Example: AVERAGEIFS('APM'#"duration", 'APM'#"env", "prod", 'APM'#"service", "web")
Available in: Sheet

MEDIAN(value1, [value2, ...])

Returns the median (middle value) of a dataset. If the dataset has an even number of values, returns the average of the two middle values.
Example: MEDIAN('APM'#"duration")
Available in: Sheet

MODE(value1, [value2, ...])

Returns the most frequently occurring value in a dataset.
Example: MODE('Logs'#"status_code")
Available in: Sheet

PERCENTILE(data, percentile)

Returns the value at a given percentile of a dataset using linear interpolation.
Example: PERCENTILE('APM'#"duration", 0.95)
Available in: Sheet

STDEV(value1, [value2, ...])

Calculates the standard deviation of a sample dataset.
Example: STDEV('APM'#"duration")
Available in: Sheet

VAR(value1, [value2, ...])

Calculates the sample variance of a dataset.
Example: VAR('APM'#"duration")
Available in: Sheet

FORECAST(x, data_y, data_x)

Predicts a future value using existing values and linear regression.
Example: FORECAST(5, {1,2,3,4}, {10,20,30,40})
Available in: Sheet

SUMPRODUCT(array1, [array2, ...])

Multiplies corresponding elements in arrays and returns the sum of those products.
Example: SUMPRODUCT({1,2,3}, {4,5,6}) => 32
Available in: Sheet

Financial

PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])

Calculates the payment for a loan based on constant payments and a constant interest rate.
Example: PMT(0.05/12, 60, 20000)
Available in: Sheet

PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning])

Calculates the present value of an investment.
Example: PV(0.05/12, 60, -377.42)
Available in: Sheet

FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])

Calculates the future value of an investment based on periodic, constant payments and a constant interest rate.
Example: FV(0.06/12, 240, -500)
Available in: Sheet

NPV(discount, cashflow1, [cashflow2, ...])

Calculates the net present value of an investment based on a discount rate and a series of future cash flows.
Example: NPV(0.10, -50000, 8000, 9200, 10400)
Available in: Sheet

IRR(cashflow_amounts, [rate_guess])

Calculates the internal rate of return for a series of cash flows.
Example: IRR({-50000, 8000, 9200, 10400, 11600, 12800})
Available in: Sheet

NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning])

Calculates the number of periods for an investment or loan.
Example: NPER(0.05/12, -377.42, 20000)
Available in: Sheet

RATE(number_of_periods, payment_amount, present_value, [future_value], [end_or_beginning], [guess])

Calculates the interest rate per period of an annuity.
Example: RATE(48, -200, 8000)
Available in: Sheet

RRI(number_of_periods, present_value, future_value)

Calculates the equivalent interest rate for the growth of an investment.
Example: RRI(10, 100, 200)
Available in: Sheet

Info

ISBLANK(value)

Tests whether a cell is blank.
Example: ISBLANK(A1)
Available in: Sheet

ISNUMBER(value)

Tests whether a value is a number.
Example: ISNUMBER(123)
Available in: Sheet

TYPE(value)

Returns the data type of a value as a number (1 = number, 2 = text, 4 = logical, 16 = error).
Example: TYPE(123) => 1
Available in: Sheet