Function reference
All 97 functions you can use in Sheets formulas, with syntax and what each does, from SUM and AVERAGE to date maths and text formatting.
Every function you can use in a Sheets formula — 97 in all. Names are not case sensitive, and the formula editor suggests them as you type.
Dates are numbers of days, so date maths works (=C2+30 is 30 days later). Show a date result in a Date column, or format it with TEXT.
Math and aggregates
| Function | Syntax | What it does |
|---|---|---|
SUM |
SUM(range, …) |
Adds the numeric cells. |
PRODUCT |
PRODUCT(range, …) |
Multiplies the numeric cells. |
AVERAGE |
AVERAGE(range, …) |
Mean of the numeric cells. #DIV/0! if there are none. |
MIN |
MIN(range, …) |
Smallest number. |
MAX |
MAX(range, …) |
Largest number. |
COUNT |
COUNT(range, …) |
How many cells hold numbers. |
COUNTA |
COUNTA(range, …) |
How many cells are not empty. |
COUNTBLANK |
COUNTBLANK(range) |
How many cells are empty. |
ROUND |
ROUND(number, [digits]) |
Rounds to a number of digits. |
ROUNDUP |
ROUNDUP(number, [digits]) |
Rounds away from zero. |
ROUNDDOWN |
ROUNDDOWN(number, [digits]) |
Rounds toward zero. |
ABS |
ABS(number) |
Absolute value. |
INT |
INT(number) |
Rounds down to the nearest integer. |
SQRT |
SQRT(number) |
Square root. |
MOD |
MOD(number, divisor) |
Remainder after division. |
POWER |
POWER(number, exponent) |
Number raised to a power. |
MEDIAN |
MEDIAN(number1, [number2], …) |
Middle value of the numbers. |
MODE |
MODE(number1, [number2], …) |
Most frequent number (#N/A when nothing repeats). |
STDEV |
STDEV(number1, [number2], …) |
Sample standard deviation. |
VAR |
VAR(number1, [number2], …) |
Sample variance. |
LARGE |
LARGE(range, k) |
k-th largest number. |
SMALL |
SMALL(range, k) |
k-th smallest number. |
RANK |
RANK(number, range, [ascending]) |
Position of number in range (largest = 1 unless ascending ≠ 0). |
CEILING |
CEILING(number, [significance]) |
Rounds up to a multiple of significance. |
FLOOR |
FLOOR(number, [significance]) |
Rounds down to a multiple of significance. |
PI |
PI() |
3.14159…. |
EXP |
EXP(number) |
e raised to number. |
LN |
LN(number) |
Natural logarithm. |
LOG |
LOG(number, [base]) |
Logarithm (base 10 by default). |
LOG10 |
LOG10(number) |
Base-10 logarithm. |
SIGN |
SIGN(number) |
1, 0 or -1. |
Conditional
| Function | Syntax | What it does |
|---|---|---|
COUNTIF |
COUNTIF(range, criteria) |
Counts cells matching a test such as ">10" or "paid". |
SUMIF |
SUMIF(range, criteria, [sum_range]) |
Sums cells matching the test. |
AVERAGEIF |
AVERAGEIF(range, criteria, [avg_range]) |
Averages cells matching the test. |
COUNTIFS |
COUNTIFS(range1, criterion1, [range2, criterion2], …) |
Counts rows meeting every criterion. |
SUMIFS |
SUMIFS(sum_range, range1, criterion1, …) |
Sums sum_range where every criterion matches. |
AVERAGEIFS |
AVERAGEIFS(avg_range, range1, criterion1, …) |
Average of avg_range where every criterion matches. |
MAXIFS |
MAXIFS(max_range, range1, criterion1, …) |
Largest value where every criterion matches. |
MINIFS |
MINIFS(min_range, range1, criterion1, …) |
Smallest value where every criterion matches. |
Logic
| Function | Syntax | What it does |
|---|---|---|
IF |
IF(condition, value_if_true, [value_if_false]) |
Branches on a condition. |
IFERROR |
IFERROR(value, value_if_error) |
Fallback when the first argument is an error. |
AND |
AND(logical1, [logical2], …) |
TRUE when every argument is TRUE. |
OR |
OR(logical1, [logical2], …) |
TRUE when any argument is TRUE. |
NOT |
NOT(logical) |
Inverts TRUE and FALSE. |
ISBLANK |
ISBLANK(value) |
TRUE when the cell is empty. |
ISNUMBER |
ISNUMBER(value) |
TRUE when the value is a number. |
ISERROR |
ISERROR(value) |
TRUE when the value is an error. |
IFS |
IFS(test1, value1, [test2, value2], …) |
Value of the first test that is TRUE; #N/A when none is. |
IFNA |
IFNA(value, value_if_na) |
value, or the fallback when it is #N/A. |
SWITCH |
SWITCH(expr, case1, result1, …, [default]) |
Result paired with the first case equal to expr. |
ISTEXT |
ISTEXT(value) |
TRUE when the value is text. |
NA |
NA() |
The #N/A error — marks a value as not available. |
TRUE and FALSE can be typed as values.
Text
| Function | Syntax | What it does |
|---|---|---|
CONCAT |
CONCAT(text1, [text2], …) |
Joins values into one text. |
CONCATENATE |
CONCATENATE(text1, [text2], …) |
Same as CONCAT. |
TEXTJOIN |
TEXTJOIN(separator, skip_empty, range, …) |
Joins values with a separator. |
LEFT |
LEFT(text, [count]) |
First characters. |
RIGHT |
RIGHT(text, [count]) |
Last characters. |
MID |
MID(text, start, count) |
Characters from the middle (start counts from 1). |
LEN |
LEN(text) |
Length of a text. |
LOWER |
LOWER(text) |
Lower case. |
UPPER |
UPPER(text) |
Upper case. |
TRIM |
TRIM(text) |
Strips surrounding spaces. |
SUBSTITUTE |
SUBSTITUTE(text, old, new) |
Replaces every occurrence of a text. |
VALUE |
VALUE(text) |
Reads a text as a number. |
PROPER |
PROPER(text) |
Capitalises Each Word. |
FIND |
FIND(find_text, within_text, [start]) |
Position of find_text (case-sensitive). |
SEARCH |
SEARCH(find_text, within_text, [start]) |
Position of find_text (ignores case). |
REPT |
REPT(text, times) |
text repeated. |
EXACT |
EXACT(text1, text2) |
TRUE when identical, case included. |
TEXT |
TEXT(value, format) |
Formats a number or date: "#,##0.00", "0%", "dd-mmm-yyyy", "mmmm yyyy". |
Date and time
| Function | Syntax | What it does |
|---|---|---|
TODAY |
TODAY() |
Today's date. |
NOW |
NOW() |
Current date and time. |
DATE |
DATE(year, month, day) |
Builds a date. |
YEAR |
YEAR(date) |
Year of a date. |
MONTH |
MONTH(date) |
Month of a date (1 to 12). |
DAY |
DAY(date) |
Day of the month (1 to 31). |
WEEKDAY |
WEEKDAY(date) |
Day of the week (1 is Sunday). |
DAYS |
DAYS(end_date, start_date) |
Whole days between two dates. |
EDATE |
EDATE(start_date, months) |
Same day, months later (or earlier). |
EOMONTH |
EOMONTH(start_date, months) |
Last day of the month, months away. |
DATEDIF |
DATEDIF(start_date, end_date, unit) |
Whole "Y" years, "M" months or "D" days between. |
NETWORKDAYS |
NETWORKDAYS(start_date, end_date) |
Weekdays (Mon–Fri) between, both ends included. |
HOUR |
HOUR(datetime) |
Hour 0–23. |
MINUTE |
MINUTE(datetime) |
Minute 0–59. |
SECOND |
SECOND(datetime) |
Second 0–59. |
Lookup
| Function | Syntax | What it does |
|---|---|---|
VLOOKUP |
VLOOKUP(value, range, column, [FALSE]) |
Finds the value in the first column of the range and returns the Nth column of that row. Matching is always exact. |
MATCH |
MATCH(value, range) |
Position of a value in a range (from 1). Exact match. |
INDEX |
INDEX(range, row, [column]) |
The value at a position inside a range. |
AXLOOKUP |
AXLOOKUP(type, key_field, key, target_field) |
One field of a record in your workspace. Runs with your permissions. |
XLOOKUP |
XLOOKUP(lookup, lookup_range, return_range, [if_not_found]) |
Value from return_range at lookup's position. |
HLOOKUP |
HLOOKUP(lookup, table, row_index) |
Looks across the first row, returns from row_index. |
ROWS |
ROWS(range) |
Number of rows in range. |
COLUMNS |
COLUMNS(range) |
Number of columns in range. |
Finance
| Function | Syntax | What it does |
|---|---|---|
PMT |
PMT(rate, periods, present_value, [future_value], [type]) |
Payment per period on a loan (negative = paid out). |
FV |
FV(rate, periods, payment, [present_value], [type]) |
Future value of regular payments. |
PV |
PV(rate, periods, payment, [future_value], [type]) |
Present value of regular payments. |
NPV |
NPV(rate, value1, [value2], …) |
Net present value of future cash flows. |