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( _logicalexpression, _value_iftrue, _value_iffalse) 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( _logicalexpression) Example: NOT(ORDERTOTAL = "100") |
| ALL() | Checks all values in the expression and returns 'true' when all values are evaluated as 'false'. Accepts multiple arguments. |
ALL( _logical_expression1, _logical_expression2,...) 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_expression1, _logical_expression2,...) 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( _value1, _value2) Example: LT(30,40) |
| GT() | Checks if the first value is larger than the second. Requires two arguments. |
GT( _value1, _value2) Example: GT(50,40) |
| LE() | Checks if the first value is less than or equal to the second. Requires two arguments. |
LE( _value1, _value2) Example: LE(20,40) |
| GE() | Checks if the first value is greater than or equal to the second. Requires two arguments. |
GE( _value1, _value2) Example: GE(30,10) |
| EQ() | Checks if two values are equal. Requires two arguments. |
EQ( _value1, _value2) Example: EQ(ORDERTOTAL,180) |
| NE() | Checks if two values are not equal. Requires two arguments. |
NE( _value1, _value2) 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, value2) 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, value2) Example: ICONTAINS("Motorcycles", "moto") |
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( _stringinput, [precision]) Examples: LEFT("fred") returns "f" 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( _stringinput, [precision]) Examples: RIGHT("fred") returns "d" RIGHT("Fred", 2) returns "ed" |
| REPLACE() | Replaces all instances of a character or string with a replacement. Case-sensitive; replaces all occurrences. |
REPLACE( _inputstring, _searchstring, _replacementstring) Examples: REPLACE("fred", "f", "F") returns "Fred" REPLACE("1, 2, 3, 4", ", ", "/") returns "1/2/3/4" |
| CONCAT() | Joins two values as a single text string. Requires two arguments. |
CONCAT( _string1, _string2) Example: CONCAT(ADDRESS,COUNTRY) |
Arithmetical functions
| Function | Description | Usage |
|---|---|---|
| SUM() | Adds up all numbers given and returns the total sum. Accepts multiple arguments. |
SUM( _value1, _value2,...) Example: SUM(10,50,40) |
| AVERAGE() | Calculates the arithmetic mean of the given numbers. Accepts multiple arguments. |
AVERAGE( _value1, _value_2,...) Example: AVERAGE(10,240,31,22) |
| SUB() | Subtracts the second number from the first and returns the result. Requires two arguments. |
SUB( _value1, _value2) 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( _value1, _value2) Example: DIV(50,40) |
| MUL() | Multiplies all given numerical values and returns the result. Requires two arguments. |
MUL( _value1, _value2) Example: MUL(30,20) |
| SUMIF | Sums the values in a range when a condition is met. Accepts multiple arguments. |
SUMIF( condition, _field1, _field2, _field3, ...) Example: SUMIF(EQ(field_1, 100), _ordervalue, _shippingvalue, 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]) Examples: ROUND( 234.23634) = 234 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]) Examples: ROUNDUP(4.123) = 5 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]) Examples: ROUNDDOWN(4.123) = 4 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( _value1, _value2) 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( _value1, _value2) 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 ABS("banana") = field appears blank ABS("banana", true) = displays error on field |
| COUNT | Counts the number of inputs passed to the function. Accepts multiple arguments. |
COUNT( _value1, _value2, …) 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(1, 10) = 1 Example: RANDBETWEEN(1, 10) |
| POWER | Raises the first value to the exponent passed as the second value. Requires two arguments. |
POWER( _value1, _value2) Example: POWER(2, 3) = 8 |
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( _value1, [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([_timeoffset]) Example: UTCNOW() UTCNOW(-5) |
| ONUPDATE | Re-checks the result of the first value if any of the provided fields is updated. Accepts multiple arguments. |
ONUPDATE( function, _field1, _field2,...) 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( _value1, _value2, unit) Example: DATEDIFF("2025-02-07", "2025-02-08", "day") |
Operators
Arithmetic operators
| Operator | Description | Usage |
|---|---|---|
| + | Adds a field or value to another. | _value1 + _value2 |
| - | Subtracts a field or value from another. | _value1 - _value2 |
| * | Multiplies a field or value by another. | _value1 * _value2 |
| / | Divides a field or value by another. | _value1 / _value2 |
Comparison operators
| Operator | Description | Usage |
|---|---|---|
| = | Compares if a field or value matches another. | _value1 = _value2 |
| < | Checks if a field or value is lower than another. | _value1 < _value2 |
| > | Checks if a field or value is greater than another. | _value1 > _value2 |
| <= | Checks if a field or value is lower than or equal to another. | _value1 <= _value2 |
| >= | Checks if a field or value is greater than or equal to another. | _value1 >= _value2 |
| != | Checks if a field or value is different from another. | _value1 != _value2 |