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.