This is a list of all the functions and operators available for Formula fields in Zapier Tables and how to use them. You can enter any value in a function, or select existing fields from the Insert Fields tab below the Formula text field. Learn how to create a Formula field.
NoteFormulas must be valid to be used in a field. Learn how to troubleshoot formulas.
Functions
Each function within a formula requires arguments (pieces of information) to complete the calculation, and the number of arguments required depends on the function you use.
Arguments can be a field from your table, a numerical value, or a string (text) that you type directly into the formula. When using a string, you must surround it with double quotation marks. For example, to check if a field contains the country France, use "France" in your formula.
Logical functions
| Function | Description | Usage |
|---|---|---|
| IF() | Checks if a logical expression is evaluated as 'true' or 'false', and returns the first or second value, depending on the result. |
IF(logical_expression, value_if_true, value_if_false) Example: IF(ORDERQUANTITY > 20, "Large order","Small order") |
| NOT() | Returns 'true' if the value is the opposite of the expression, and 'false' if it's the same as the expression. Only accepts one argument. |
NOT(logical_expression) Example: NOT(ORDERTOTAL = "100") |
| ALL() | Checks all values in the expression and returns 'true' when all values are evaluated as 'true'. Accepts multiple arguments. |
ALL(logical_expression_1, logical_expression_2, ...) Example: ALL(ORDERTOTAL > "100", ORDERQUANTITY > "20", COUNTRY = "USA") |
| ANY() | Checks all values in the expression and returns 'true' if any is 'true'. Accepts multiple arguments. |
ANY(logical_expression_1, logical_expression_2, ...) Example: ANY(ORDERTOTAL < "50", ORDERQUANTITY < "10") |
| ISTEXT() | Checks if the value is a string. Only accepts one argument. | ISTEXT(value) |
| ISNUMBER() | Checks if the value is a number. Only accepts one argument. | ISNUMBER(value) |
| ISBOOL() | Checks if the value is a boolean (true or false). Only accepts one argument. | ISBOOL(value) |
| ISNULL() | Checks if the value is NULL and returns 'true' if so. Only accepts one argument. | ISNULL(value) |
| LT() | Checks if the first value is smaller than the second. Requires two arguments. |
LT(value_1, value_2) Example: LT(30, 40) |
| GT() | Checks if the first value is larger than the second. Requires two arguments. |
GT(value_1, value_2) Example: GT(50, 40) |
| LE() | Checks if the first value is less than or equal to the second. Requires two arguments. |
LE(value_1, value_2) Example: LE(20, 40) |
| GE() | Checks if the first value is greater than or equal to the second. Requires two arguments. |
GE(value_1, value_2) Example: GE(30, 10) |
| EQ() | Checks if two values are equal. Requires two arguments. |
EQ(value_1, value_2) Example: EQ(ORDERTOTAL, 180) |
| NE() | Checks if two values are not equal. Requires two arguments. |
NE(value_1, value_2) Example: NE(COUNTRY, "SPAIN") |
| CONTAINS() | Checks if the first value contains the second value and returns 'true' if so. Case-sensitive. Requires two arguments. |
CONTAINS(value_1, value_2) Example: CONTAINS("Motorcycles", "Moto") |
| ICONTAINS() | Checks if the first value contains the second value and returns 'true' if so. Not case-sensitive. Requires two arguments. |
ICONTAINS(value_1, value_2) Example: ICONTAINS("Motorcycles", "moto") |
| MULTI_DROPDOWN_CONTAINS() | Checks if a string matches any label value in a multi dropdown field. Case-sensitive. |
MULTI_DROPDOWN_CONTAINS(dropdown field name, string) Example: MULTI_DROPDOWN_CONTAINS(STATUS_FIELD, "Active") returns true if "Active" is selected |
| MULTI_DROPDOWN_ICONTAINS() | Checks if a string matches any label value in a multi dropdown field. Not case-sensitive. |
MULTI_DROPDOWN_ICONTAINS(dropdown field name, string) Example: MULTI_DROPDOWN_ICONTAINS(STATUS_FIELD, "active") returns true if "Active" is selected |
String functions
| Function | Description | Usage |
|---|---|---|
| LEFT() | Returns the leftmost part of a string. Without precision returns first character, with precision returns specified number of characters from the left. |
LEFT(string_input, [precision]) Example: LEFT("fred", 2) returns "fr" |
| RIGHT() | Returns the rightmost part of a string. Without precision returns last character, with precision returns specified number of characters from the right. |
RIGHT(string_input, [precision]) Example: RIGHT("Fred", 2) returns "ed" |
| REPLACE() | Replaces all instances of a character or string with a replacement. Case-sensitive; replaces all occurrences. |
REPLACE(input_string, search_string, replacement_string) Example: REPLACE("fred", "f", "F") returns "Fred" |
| CONCAT() | Joins two values as a single text string. Requires two arguments. |
CONCAT(string_1, string_2) Example: CONCAT(ADDRESS, COUNTRY) |
| LEN() | Returns the length of a given input. Works with strings, arrays, objects, or numbers (converted to strings). Returns 0 for null or boolean values. |
LEN(value) Example: LEN("my string") returns 9 |
| FIND() | Returns the one-indexed position where a string is first found within text. Returns 0 if not found. Optionally accepts a starting position. |
FIND(string_to_find, text_to_search, [starting_at]) Example: FIND("name", "my name is mud") returns 4 |
| SPLIT() | Splits a string into an array using a delimiter. Default delimiter is a comma. |
SPLIT(input_string, [delimiter]) Example: SPLIT("1 2 3 4", " ") returns ["1", "2", "3", "4"] |
Arithmetical functions
| Function | Description | Usage |
|---|---|---|
| SUM() | Adds up all numbers given and returns the total sum. Accepts multiple arguments. |
SUM(value_1, value_2, ...) Example: SUM(10, 50, 40) |
| AVERAGE() | Calculates the arithmetic mean of the given numbers. Accepts multiple arguments. |
AVERAGE(value_1, value_2, ...) Example: AVERAGE(10, 240, 31, 22) |
| SUB() | Subtracts the second number from the first and returns the result. Requires two arguments. |
SUB(value_1, value_2) Example: SUB(20, 15) |
| DIV() | Divides the first number by the second and returns the result. Returns an error if dividing by zero. Requires two arguments. |
DIV(value_1, value_2) Example: DIV(50, 40) |
| MUL() | Multiplies all given numerical values and returns the result. Requires two arguments. |
MUL(value_1, value_2) Example: MUL(30, 20) |
| SUMIF | Sums the values in a range when a condition is met. Accepts multiple arguments. |
SUMIF(condition, field_1, field_2, ...) Example: SUMIF(EQ(field_1, 100), order_value, shipping_value, tax) |
| ROUND | Rounds the provided value to a specified precision, in decimal places. If no precision is set, rounds to the nearest whole number. |
ROUND(value, [precision]) Example: ROUND(234.23634, 2) = 234.24 |
| ROUNDUP | Rounds the provided value up to a specified precision, in decimal places. If no precision is set, rounds to the nearest whole number. |
ROUNDUP(value, [precision]) Example: ROUNDUP(4.1233, 3) = 4.124 |
| ROUNDDOWN | Rounds the provided value down to a specified precision, in decimal places. If no precision is set, rounds to the nearest whole number. |
ROUNDDOWN(value, [precision]) Example: ROUNDDOWN(4.123, 1) = 4.1 |
| CEILING | Divides the first number by the second and rounds up to the nearest whole number. Requires two arguments. |
CEILING(value_1, value_2) Example: CEILING(23, 2) = 12 |
| FLOOR | Divides the first number by the second and rounds down to the nearest whole number. Requires two arguments. |
FLOOR(value_1, value_2) Example: FLOOR(23, 2) = 11 |
| ABS | Returns the absolute value of a number. Optionally, add "true" as a second argument to display an error message when the value is not a number. |
ABS(value, [error]) Example: ABS(-234.23634) = 234.23634 |
| COUNT | Counts the number of inputs passed to the function. Accepts multiple arguments. |
COUNT(value_1, value_2, ...) Example: COUNT(2, 3, 10, 2) = 4 |
| RAND | Returns a random number between 0 and 1. Does not require arguments. | RAND() |
| RANDBETWEEN | Returns a random integer between a lower and upper range. Requires two arguments. |
RANDBETWEEN(lower, upper) Example: RANDBETWEEN(1, 10) |
| POWER | Raises the first value to the exponent passed as the second value. Requires two arguments. |
POWER(value_1, value_2) Example: POWER(2, 3) = 8 |
| COUNTBLANK() | Counts the number of blank or null values passed to the function. For arrays, counts blank elements within the array. Accepts multiple arguments. |
COUNTBLANK(value_1, value_2, ...) Example: COUNTBLANK(2, null, "", 2) returns 2 |
| EVEN() | Returns the nearest even number rounded up from the given value. |
EVEN(number) Example: EVEN(1) returns 2 |
| ODD() | Returns the nearest odd number rounded up from the given value. |
ODD(number) Example: ODD(2) returns 3 |
| INT() | Returns the nearest integer of the given number rounded down (floor operation). |
INT(number) Example: INT(45.6) returns 45 |
| PI() | Returns pi (π) to a specified number of decimal places (0-50). Defaults to 14 decimal places if not provided. |
PI([n_places]) Example: PI(2) returns 3.14 |
| SIGN() | Returns the sign of the given number: -1 if negative, 0 if zero, 1 if positive. |
SIGN(value) Example: SIGN(-23.2) returns -1 |
| BASE() | Converts a number to a different base representation (2-36). Optionally pads with leading zeros. |
BASE(value, base, [min_digits]) Example: BASE(255, 16) returns "FF" |
Date and time functions
| Function | Description | Usage |
|---|---|---|
| TODATETIME() | Converts a value into ISO8601 date and time format. Optionally, add "true" as a second argument to display n/a instead of an error when the date and time cannot be converted. |
TODATETIME(value, [error]) Example: TODATETIME(ORDERDATE, false) |
| UTCNOW | Returns the current time's ISO 8601 UTC timestamp. Optionally, pass an hour offset to indicate the timezone for the timestamp. |
UTCNOW([time_offset]) Example: UTCNOW(-5) |
| ONUPDATE | Re-checks the result of the first value if any of the provided fields is updated. Accepts multiple arguments. |
ONUPDATE(function, field_1, field_2, ...) Example: ONUPDATE(EQ(DEPT_NAME, "HR"), TICKET_STATUS, RESOLUTION) |
| DATEDIFF | Compares two timestamps and calculates the time difference between them in the unit defined. Accepts the following units: Minute, Hour, Day, Month, Year |
DATEDIFF(value_1, value_2, unit) Example: DATEDIFF("2025-02-07", "2025-02-08", "day") |
Validation functions
| Function | Description | Usage |
|---|---|---|
| ISBLANK() | Checks if the value is an empty string and returns 'true' if so. Note: null values are not considered blank—use ISNULL() for null checks. |
ISBLANK(value) Examples: ISBLANK("") returns true ISBLANK(null) returns false |
| ISDATE() | Checks if the value is a valid date string and returns 'true' if so. The date must include a year to be valid. Dates with time components are not considered valid. |
ISDATE(date_string) Examples: ISDATE("July 18 1955") returns true ISDATE("January") returns false |
| ISEMAIL() | Checks if the value is a valid email address format and returns 'true' if so. |
ISEMAIL(value) Examples: ISEMAIL("test@zapier.com") returns true ISEMAIL("@zapier.com") returns false |
| ISEVEN() | Checks if the given value is an even number. Returns 'false' for non-numeric values. |
ISEVEN(value) Examples: ISEVEN(42) returns true ISEVEN(1) returns false |
| ISODD() | Checks if the given value is an odd number. Returns 'false' for non-numeric values. |
ISODD(value) Examples: ISODD(67) returns true ISODD(12.2) returns false |
| REGEXMATCH() | Checks if the value matches a regular expression pattern and returns 'true' if so. Performs a full match, not partial. |
REGEXMATCH(value, regex_pattern) Examples: REGEXMATCH("test@zapier.com", ".*@.*\.com") returns true REGEXMATCH("hello", "world") returns false |
Meta functions
| Function | Description | Usage |
|---|---|---|
| RECORD_ID() | Returns the unique record ID from the current record. Does not require arguments. |
RECORD_ID() Example: RECORD_ID() returns "01KC1PZQ8VF270JAYK0N7BSSPM" |
Utility functions
| Function | Description | Usage |
|---|---|---|
| NULL() | Returns a null value. Useful for explicitly setting a field to null or clearing values. Any arguments passed are ignored. |
NULL() Example: NULL() returns null |
Operators
Arithmetic operators
| Operator | Description | Usage |
|---|---|---|
| + | Adds a field or value to another. | value_1 + value_2 |
| - | Subtracts a field or value from another. | value_1 - value_2 |
| * | Multiplies a field or value by another. | value_1 * value_2 |
| / | Divides a field or value by another. | value_1 / value_2 |
Comparison operators
| Operator | Description | Usage |
|---|---|---|
| = | Compares if a field or value matches another. | value_1 = value_2 |
| < | Checks if a field or value is lower than another. | value_1 < value_2 |
| > | Checks if a field or value is greater than another. | value_1 > value_2 |
| <= | Checks if a field or value is lower than or equal to another. | value_1 <= value_2 |
| >= | Checks if a field or value is greater than or equal to another. | value_1 >= value_2 |
| != | Checks if a field or value is different from another. | value_1 != value_2 |