Google Sheets Functions

This page groups together the most frequently used functions, explained with a simple example.

Array

SUMPRODUCT	Calculates the sum of the products of several ranges.
TRANSPOSE	Returns a range with rows and columns swapped.

Date

DATEDIF	Calculates the number of days, months, or years between two dates.
TODAY	Returns the current date.

Filter

FILTER	Returns a filtered version of a range where rows or columns meet specified conditions.
SORT	Sorts the rows of a range by one or multiple columns.
UNIQUE	Returns the unique rows in a range, excluding duplicates.

Google

ARRAYFORMULA	Displays values from an array formula over multiple rows and/or columns.
GOOGLETRANSLATE	Translates text into the specified language.
QUERY	Performs a query on all the data in a range and returns a data array.
SPARKLINE	Inserts a miniature chart within a cell.

Logical

AND	Checks if all tests are TRUE.
BYCOL	Applies a LAMBDA function to each column of an array.
BYROW	Applies a LAMBDA function to each row of an array.
IF	Returns a value based on a condition.
IFERROR	Returns a value if there is no error or another value in case of an error.
IFS	Evaluates multiple conditions and displays the value corresponding to the first true condition.
LAMBDA	Creates and returns a custom function.
LET	Assigns names to values and then returns the result of a formula.
MAKEARRAY	Returns an array with specified dimensions with values calculated by a LAMBDA function.
MAP	Applies a LAMBDA function to each value in an array.
OR	Checks if at least one of the tests is TRUE.
REDUCE	Reduces an array to a cumulative result by applying a LAMBDA function.
SCAN	Scans an array and produces intermediate values by applying a LAMBDA function.

Lookup

HLOOKUP	Returns a value from a range based on a value found in the first row.
INDEX	Returns a value in a range based on a column and row number.
INDEX + MATCH	A combination similar to VLOOKUP and HLOOKUP without the constraint of the first column or row.
MATCH	Returns the position of a value in a range.
VLOOKUP	Returns a value from a range based on a value found in the first column.
XLOOKUP	Searches for a value in a range and returns the value in the same position from another range.
XMATCH	Returns the position of a value in a range.

Math

CEILING	Rounds a number up.
COUNTBLANK	Returns the number of empty cells.
COUNTIF	Returns the number of cells that match a criterion.
COUNTIFS	Returns the number of cells that meet multiple criteria.
FLOOR	Rounds a number down.
MROUND	Rounds a number to the nearest multiple of another number.
RAND	Generates a random number.
RANDBETWEEN	Generates a random integer between two specified values, inclusive.
ROUND	Rounds a number.
SQRT	Calculates the square root of a number.
SUM	Calculates the sum of a range of numbers.
SUMIF	Calculates the sum based on a criterion.
SUMIFS	Calculates the sum based on multiple criteria.

Operator

Checks if a value is within a range of values.

Statistical

AVERAGE	Calculates the average of a set of values.
COUNT	Returns the number of cells that contain numbers.
COUNTA	Returns the number of non-empty cells.
MAX	Returns the maximum value.
MIN	Returns the minimum value.
RANK	Assigns a rank to a value based on a set of values.

Text

CONCATENATE	Joins several values together.
JOIN	Joins the values of one or more ranges and allows defining a delimiter.
LEFT	Extracts characters from the left side of a text string.
LOWER	Converts text to lowercase.
MID	Extracts characters from a text string.
PROPER	Converts the first letter of each word to uppercase and the rest to lowercase.
REGEXEXTRACT	Extracts one or more parts of a text using regular expressions.
REGEXMATCH	Checks if a text matches a regular expression.
REGEXREPLACE	Replaces one or more parts of a text using regular expressions.
RIGHT	Extracts characters from the right side of a text string.
SPLIT	Splits a text based on a delimiter and places each fragment into a cell.
SUBSTITUTE	Replaces values with others.
TEXTJOIN	Joins the values of one or more ranges, allows defining a delimiter, and ignores empty values.
TRIM	Removes unnecessary spaces.
UPPER	Converts text to uppercase.

Web

IMPORTHTML	Imports data from a table or list on a web page.
IMPORTRANGE	Imports a range of cells from a specified spreadsheet.