Google Sheets Functions

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

Array

SUMPRODUCTCalculates the sum of the products of several ranges.
TRANSPOSEReturns a range with rows and columns swapped.

Date

DATEDIFCalculates the number of days, months, or years between two dates.
TODAYReturns the current date.

Filter

FILTERReturns a filtered version of a range where rows or columns meet specified conditions.
SORTSorts the rows of a range by one or multiple columns.
UNIQUEReturns the unique rows in a range, excluding duplicates.

Google

ARRAYFORMULADisplays values from an array formula over multiple rows and/or columns.
GOOGLETRANSLATETranslates text into the specified language.
QUERYPerforms a query on all the data in a range and returns a data array.
SPARKLINEInserts a miniature chart within a cell.

Logical

ANDChecks if all tests are TRUE.
BYCOLApplies a LAMBDA function to each column of an array.
BYROWApplies a LAMBDA function to each row of an array.
IFReturns a value based on a condition.
IFERRORReturns a value if there is no error or another value in case of an error.
IFSEvaluates multiple conditions and displays the value corresponding to the first true condition.
LAMBDACreates and returns a custom function.
LETAssigns names to values and then returns the result of a formula.
MAKEARRAYReturns an array with specified dimensions with values calculated by a LAMBDA function.
MAPApplies a LAMBDA function to each value in an array.
ORChecks if at least one of the tests is TRUE.
REDUCEReduces an array to a cumulative result by applying a LAMBDA function.
SCANScans an array and produces intermediate values by applying a LAMBDA function.

Lookup

HLOOKUPReturns a value from a range based on a value found in the first row.
INDEXReturns a value in a range based on a column and row number.
INDEX + MATCHA combination similar to VLOOKUP and HLOOKUP without the constraint of the first column or row.
MATCHReturns the position of a value in a range.
VLOOKUPReturns a value from a range based on a value found in the first column.
XLOOKUPSearches for a value in a range and returns the value in the same position from another range.
XMATCHReturns the position of a value in a range.

Math

CEILINGRounds a number up.
COUNTBLANKReturns the number of empty cells.
COUNTIFReturns the number of cells that match a criterion.
COUNTIFSReturns the number of cells that meet multiple criteria.
FLOORRounds a number down.
MROUNDRounds a number to the nearest multiple of another number.
RANDGenerates a random number.
RANDBETWEENGenerates a random integer between two specified values, inclusive.
ROUNDRounds a number.
SQRTCalculates the square root of a number.
SUMCalculates the sum of a range of numbers.
SUMIFCalculates the sum based on a criterion.
SUMIFSCalculates the sum based on multiple criteria.

Operator

ISBETWEENChecks if a value is within a range of values.

Statistical

AVERAGECalculates the average of a set of values.
COUNTReturns the number of cells that contain numbers.
COUNTAReturns the number of non-empty cells.
MAXReturns the maximum value.
MINReturns the minimum value.
RANKAssigns a rank to a value based on a set of values.

Text

CONCATENATEJoins several values together.
JOINJoins the values of one or more ranges and allows defining a delimiter.
LEFTExtracts characters from the left side of a text string.
LOWERConverts text to lowercase.
MIDExtracts characters from a text string.
PROPERConverts the first letter of each word to uppercase and the rest to lowercase.
REGEXEXTRACTExtracts one or more parts of a text using regular expressions.
REGEXMATCHChecks if a text matches a regular expression.
REGEXREPLACEReplaces one or more parts of a text using regular expressions.
RIGHTExtracts characters from the right side of a text string.
SPLITSplits a text based on a delimiter and places each fragment into a cell.
SUBSTITUTEReplaces values with others.
TEXTJOINJoins the values of one or more ranges, allows defining a delimiter, and ignores empty values.
TRIMRemoves unnecessary spaces.
UPPERConverts text to uppercase.

Web

IMPORTHTMLImports data from a table or list on a web page.
IMPORTRANGEImports a range of cells from a specified spreadsheet.