Scoop Formula Reference

This guide lists all the functions supported by Scoop's Calculated Field engine. Scoop uses a spreadsheet-compatible syntax (similar to Excel/Google Sheets).

🧮 Mathematical Functions

Function	Syntax	Description
SUM	`SUM(value1, [value2, ...])`	Adds all the numbers in a range of cells.
SUMIF	`SUMIF(range, criterion, [sum_range])`	Returns a conditional sum across a range.
SUMIFS	`SUMIFS(sum_range, criteria_range1, criterion1, ...)`	Returns the sum of a range depending on multiple criteria.
SUMPRODUCT	`SUMPRODUCT(array1, [array2, ...])`	Multiplies corresponding components in the given arrays, and returns the sum of those products.
AVERAGE	`AVERAGE(value1, [value2, ...])`	Returns the numerical average value in a dataset, ignoring text.
MEDIAN	`MEDIAN(value1, [value2, ...])`	Returns the median value in a numeric dataset.
COUNT	`COUNT(value1, [value2, ...])`	Returns the count of the number of numeric values in a dataset.
COUNTA	`COUNTA(value1, [value2, ...])`	Returns the count of the number of values in a dataset.
COUNTIF	`COUNTIF(range, criterion)`	Returns a conditional count across a range.
COUNTIFS	`COUNTIFS(criteria_range1, criterion1, ...)`	Returns the count of a range depending on multiple criteria.
MAX	`MAX(value1, [value2, ...])`	Returns the maximum value in a numeric dataset.
MIN	`MIN(value1, [value2, ...])`	Returns the minimum value in a numeric dataset.
ABS	`ABS(value)`	Returns the absolute value of a number.
ROUND	`ROUND(value, places)`	Rounds a number to a certain number of decimal places.
ROUNDUP	`ROUNDUP(value, places)`	Rounds a number to a certain number of decimal places, always rounding up to the next valid increment.
ROUNDDOWN	`ROUNDDOWN(value, places)`	Rounds a number to a certain number of decimal places, always rounding down to the next valid increment.
CEILING	`CEILING(value, [factor])`	Rounds a number up to the nearest integer multiple of specified significance.
FLOOR	`FLOOR(value, [factor])`	Rounds a number down to the nearest integer multiple of specified significance.
INT	`INT(value)`	Rounds a number down to the nearest integer that is less than or equal to it.
MOD	`MOD(dividend, divisor)`	Returns the result of the modulo operator, the remainder after a division operation.
POWER	`POWER(base, exponent)`	Returns a number raised to a power.
SQRT	`SQRT(value)`	Returns the positive square root of a positive number.
LOG	`LOG(value, [base])`	Returns the logarithm of a number given a base.
LOG10	`LOG10(value)`	Returns the logarithm of a number, base 10.
LN	`LN(value)`	Returns the logarithm of a number, base e (Euler's number).
EXP	`EXP(exponent)`	Returns Euler's number, e (~2.718) raised to a power.
RANDBETWEEN	`RANDBETWEEN(low, high)`	Returns a uniformly random integer between two values, inclusive.

🧠 Logical Functions

Function	Syntax	Description
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`.
IFS	`IFS(condition1, value1, [condition2, value2, ...])`	Evaluates multiple conditions and returns a value that corresponds to the first true condition.
IFERROR	`IFERROR(value, value_if_error)`	Returns the first argument if it is not an error value, otherwise returns the second argument.
IFNA	`IFNA(value, value_if_na)`	Returns the first argument if it is not an `#N/A` value, otherwise returns the second argument.
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.
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.
XOR	`XOR(logical_expression1, [logical_expression2, ...])`	Returns true if an odd number of the provided arguments are logically true, and false otherwise.
NOT	`NOT(logical_expression)`	Returns the opposite of a logical value - `NOT(TRUE)` returns `FALSE`; `NOT(FALSE)` returns `TRUE`.
TRUE	`TRUE()`	Returns the boolean value `TRUE`.
FALSE	`FALSE()`	Returns the boolean value `FALSE`.

📝 Text Functions

Function	Syntax	Description
CONCATENATE	`CONCATENATE(string1, [string2, ...])`	Appends strings to one another. (Use `&` operator as a shortcut).
TEXTJOIN	`TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])`	Combines the text from multiple strings and/or arrays, with a specifiable delimiter separating each text.
LEFT	`LEFT(string, [number_of_characters])`	Returns a substring from the beginning of a specified string.
RIGHT	`RIGHT(string, [number_of_characters])`	Returns a substring from the end of a specified string.
MID	`MID(string, starting_at, extract_length)`	Returns a segment of a string.
LEN	`LEN(text)`	Returns the length of a string.
FIND	`FIND(search_for, text_to_search, [starting_at])`	Returns the position at which a string is first found within text (case-sensitive).
SEARCH	`SEARCH(search_for, text_to_search, [starting_at])`	Returns the position at which a string is first found within text (case-insensitive).
LOWER	`LOWER(text)`	Converts a specified string to lowercase.
UPPER	`UPPER(text)`	Converts a specified string to uppercase.
PROPER	`PROPER(text)`	Capitalizes each word in a specified string.
TRIM	`TRIM(text)`	Removes leading and trailing spaces in a specified string.
SUBSTITUTE	`SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])`	Replaces existing text with new text in a string.
REPLACE	`REPLACE(text, position, length, new_text)`	Replaces part of a text string with a different text string.
TEXT	`TEXT(number, format)`	Converts a number into text according to a specified format.
TEXTAFTER	`TEXTAFTER(text, delimiter)`	Returns text that occurs after a given character or string.
TEXTBEFORE	`TEXTBEFORE(text, delimiter)`	Returns text that occurs before a given character or string.
REGEXREPLACE	`REGEXREPLACE(text, regular_expression, replacement)`	Replaces part of a text string with a different text string using regular expressions.

📅 Date & Time Functions

Function	Syntax	Description
TODAY	`TODAY()`	Returns the current date as a date value.
NOW	`NOW()`	Returns the current date and time as a date value.
DATE	`DATE(year, month, day)`	Converts a provided year, month, and day into a date.
YEAR	`YEAR(date)`	Returns the year component of a specific date.
MONTH	`MONTH(date)`	Returns the month component of a specific date.
DAY	`DAY(date)`	Returns the day component of a specific date.
HOUR	`HOUR(time)`	Returns the hour component of a specific time.
MINUTE	`MINUTE(time)`	Returns the minute component of a specific time.
SECOND	`SECOND(time)`	Returns the second component of a specific time.
DATEDIF	`DATEDIF(start_date, end_date, unit)`	Calculates the number of days, months, or years between two dates.
EDATE	`EDATE(start_date, months)`	Returns a date a specified number of months before or after another date.
EOMONTH	`EOMONTH(start_date, months)`	Returns a date representing the last day of a month which falls a specified number of months before or after another date.
NETWORKDAYS	`NETWORKDAYS(start_date, end_date, [holidays])`	Returns the number of net working days between two provided days.
WEEKDAY	`WEEKDAY(date, [type])`	Returns a number representing the day of the week of the date provided.
WEEKNUM	`WEEKNUM(date, [type])`	Returns a number representing the week of the year where the provided date falls.

🔎 Lookup & Filter Functions

Function	Syntax	Description
VLOOKUP	`VLOOKUP(search_key, range, index, [is_sorted])`	Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.
HLOOKUP	`HLOOKUP(search_key, range, index, [is_sorted])`	Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.
XLOOKUP	`XLOOKUP(search_key, lookup_range, return_range, ...)`	Modern, flexible lookup. Searches a range or an array, and then returns the item corresponding to the first match it finds.
MATCH	`MATCH(search_key, range, [search_type])`	Returns the relative position of an item in a range that matches a specified value.
INDEX	`INDEX(reference, [row], [column])`	Returns the content of a cell, specified by row and column offset.
FILTER	`FILTER(range, condition1, [condition2, ...])`	Returns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
UNIQUE	`UNIQUE(range)`	Returns unique rows in the provided source range, discarding duplicates.
SORT	`SORT(range, sort_column, is_ascending, ...)`	Sorts the rows of a given array or range by the values in one or more columns.
CHOOSE	`CHOOSE(index, choice1, [choice2, ...])`	Returns an element from a list of choices based on index.
SWITCH	`SWITCH(expression, case1, value1, [case2, value2, ...], [default])`	Tests an expression against a list of cases and returns the corresponding value of the first matching case.

🤖 AI & Scoop Special Functions

Scoop includes powerful AI and data transformation functions not found in standard spreadsheets.

Function	Syntax	Description
SCOOPPROMPT	`SCOOPPROMPT("prompt", input_cell)`	Sends the text in `input_cell` to an LLM with your custom `prompt`.
SCOOPJSON	`SCOOPJSON(json_string, key)`	Parses a JSON string and extracts the value for `key`.
SCOOPLOOKUP	`SCOOPLOOKUP(value, dataset, lookup_col, result_col)`	Cross-dataset lookup. Finds `value` in `dataset`'s `lookup_col` and returns `result_col`.
SCOOPAPPLYMODEL	`SCOOPAPPLYMODEL(model_id)`	Runs a pre-trained Scoop Machine Learning model on the current row.