Google Sheets Formulas Cheat Sheet (50 Functions with Examples)

This page gathers the Google Sheets functions people use most, grouped by task. Each row has a short explanation, an example you can copy, and a link to a step-by-step guide with screenshots. Bookmark it and come back whenever you need a formula.

How to read the examples: they assume a small table with labels in column A and numbers in column B, starting in row 2. Change the ranges to match your own data.

Totals and basic maths

Function	What it does	Example
SUM	Adds numbers	`=SUM(B2:B10)`
AVERAGE	Mean of numbers	`=AVERAGE(B2:B10)`
MIN / MAX	Smallest / largest value	`=MAX(B2:B10)`
MEDIAN	Middle value	`=MEDIAN(B2:B10)`
ROUND	Rounds to N decimals	`=ROUND(B2, 2)`
ROUNDUP / ROUNDDOWN	Always up / always down	`=ROUNDUP(B2, 0)`
MROUND	Nearest multiple	`=MROUND(B2, 0.05)`
SUBTOTAL	Total of visible rows only	`=SUBTOTAL(109, B2:B10)`
Percentage of total	Share of a whole	`=B2/SUM($B$2:$B$10)`
Percentage change	Growth between two values	`=(B3-B2)/B2`

Totals and counts with conditions

Function	What it does	Example
SUMIF	Adds rows matching one condition	`=SUMIF(A2:A10, "Food", B2:B10)`
SUMIFS	Adds rows matching several conditions	`=SUMIFS(C2:C10, A2:A10, "Food", B2:B10, "Card")`
COUNTIF	Counts rows matching one condition	`=COUNTIF(A2:A10, "Food")`
COUNTIFS	Counts rows matching several conditions	`=COUNTIFS(A2:A10, "Food", B2:B10, ">50")`
AVERAGEIF	Average of matching rows	`=AVERAGEIF(A2:A10, "Food", B2:B10)`
COUNTA	Counts non-empty cells	`=COUNTA(A2:A10)`
COUNT	Counts numbers only	`=COUNT(B2:B10)`
COUNTBLANK	Counts empty cells	`=COUNTBLANK(A2:A10)`
COUNTUNIQUE	Counts distinct values	`=COUNTUNIQUE(A2:A10)`

Logic

Function	What it does	Example
IF	One result if true, another if false	`=IF(B2>=50, "Pass", "Fail")`
IFS	Several conditions in order	`=IFS(B2>=90, "A", B2>=70, "B", TRUE, "C")`
AND / OR	Combine conditions	`=IF(AND(B2>50, C2="Paid"), "OK", "")`
IFERROR	Replaces any error	`=IFERROR(B2/C2, "")`
IFNA	Replaces only #N/A	`=IFNA(VLOOKUP(E2, A2:B10, 2, FALSE), "Not found")`

Lookups

Function	What it does	Example
VLOOKUP	Finds a row and returns a column to the right	`=VLOOKUP(E2, A2:C10, 3, FALSE)`
XLOOKUP	Modern lookup, any direction	`=XLOOKUP(E2, A2:A10, C2:C10, "Not found")`
INDEX + MATCH	Flexible classic lookup	`=INDEX(C2:C10, MATCH(E2, A2:A10, 0))`
Lookup on another tab	Search a different sheet	`=VLOOKUP(E2, Products!A:C, 3, FALSE)`
IMPORTRANGE	Pull data from another file	`=IMPORTRANGE("url", "Sheet1!A1:C10")`

Filtering, sorting and summarising

Function	What it does	Example
FILTER	Returns rows that meet a condition	`=FILTER(A2:C10, B2:B10>50)`
UNIQUE	Removes duplicates from a list	`=UNIQUE(A2:A10)`
SORT	Sorted copy of a range	`=SORT(A2:C10, 2, FALSE)`
QUERY	SQL-style filtering and grouping	`=QUERY(A1:C10, "SELECT A, SUM(B) GROUP BY A", 1)`
TRANSPOSE	Swaps rows and columns	`=TRANSPOSE(A1:C4)`
ARRAYFORMULA	One formula for a whole column	`=ARRAYFORMULA(B2:B*C2:C)`
RANK	Position in a list	`=RANK(B2, $B$2:$B$10)`

Text

Function	What it does	Example
& / CONCATENATE	Joins text	`=A2&" "&B2`
TEXTJOIN	Joins a range with a separator	`=TEXTJOIN(", ", TRUE, A2:A10)`
SPLIT	Splits text into columns	`=SPLIT(A2, " ")`
TRIM	Removes extra spaces	`=TRIM(A2)`
UPPER / LOWER / PROPER	Changes capitalisation	`=PROPER(A2)`
LEFT / RIGHT / MID	Part of a text	`=LEFT(A2, 3)`
LEN	Number of characters	`=LEN(A2)`
SUBSTITUTE	Replaces text inside a cell	`=SUBSTITUTE(A2, "-", "")`
VALUE	Turns text into a number	`=VALUE(A2)`
HYPERLINK	Clickable link	`=HYPERLINK("https://example.com", "Open")`

Dates and times

Function	What it does	Example
TODAY / NOW	Current date / date and time	`=TODAY()`
Days between dates	Subtract two dates	`=C2-B2`
DATEDIF	Difference in days, months or years	`=DATEDIF(B2, TODAY(), "Y")`
NETWORKDAYS	Working days between dates	`=NETWORKDAYS(B2, C2)`
DATE	Builds a date from parts	`=DATE(2026, 12, 31)`
YEAR / MONTH / DAY	Part of a date	`=MONTH(B2)`
EOMONTH	Last day of a month	`=EOMONTH(B2, 0)`
TEXT	Date formatted as text	`=TEXT(B2, "d mmm yyyy")`

Random numbers and mini charts

Function	What it does	Example
RANDBETWEEN	Random whole number	`=RANDBETWEEN(1, 100)`
RAND	Random decimal from 0 to 1	`=RAND()`
SPARKLINE	Tiny chart inside a cell	`=SPARKLINE(B2:G2)`
SEQUENCE	List of numbers	`=SEQUENCE(10)`

When a formula shows an error

Error	Usual cause
#N/A	A lookup didn't find the value
#DIV/0!	Dividing by zero or an empty cell
#VALUE!	Text where a number is expected
#NAME?	Misspelled function or text without quotes
#REF!	Deleted reference or blocked array result
Circular dependency	A formula includes its own cell

Formula tips worth knowing

Lock references with $ so they don't move when you copy a formula: $B$2. Press F4 while editing to add them. See absolute references.
Text always goes in double quotes inside formulas: "Food", never Food.
Show every formula at once with Ctrl + `. More in keyboard shortcuts.
Separators depend on the locale. Some countries use ; instead of , between arguments. Check File → Settings → Locale if a copied formula won't work.