# Formula Reference

All functions supported by Scoop's Calculated Field engine

# 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. |