Functions

A listing, by category, of the functions in Google Sheets. We explain how they work and when you may want to use them.

NamePurposeCategoryInputs
ARRAYFORMULArepeats non-array formulasGooglearray_formula
AVERAGEcalculates the mean of a set of numbersStatisticalnumbers
AVERAGEAcalculates the mean of a set of numbers, text, and boolean valuesStatisticalvalues
COUNTcounts the numeric values in a data setStatisticalnumbers
COUNTAcounts the non-blank values in a data setStatisticalvalues
COUNTIFcounts cells that match a criterionMathvalues
COUNTIFScounts cells that meet multiple criteriaMathvalues
COUNTUNIQUEcounts the number of unique valuesMathvalues
COUNTUNIQUEIFScounts the number of unique values based on conditionsMathvalues
DATEreturns the inputs as a dateTime and Dateyear, month, day
DATEDIFreturns the number of days, months, or years between two daysTime and Datestart date, end date, unit
DATEVALUEreturns a serial number from any valid dateTime and Datedate in text format
DAYreturns a date’s day value as a numberTime and Datedate
DAYS360returns the difference between two dates using the 360-day methodTime and Datestart date, end date, [method]
DAYSreturns the number of days between two datesTime and Dateend date, start date
DBreturns one period of accelerated depreciationFinancialcost, salvage value, life, period, [month]
DDBreturns one period of accelerated depreciationFinancialcost, salvage value, life, period, [factor]
EDATEreturns a date a given number of months away from a specified dateTime and Datestart date, # of months
EOMONTHreturns end date of a month x months away from a dateTime and Datestart date, # of months
FILTERreturns filtered rows from a tableFilterrange,condition1,[condition2…]
GOOGLEFINANCEimports security data from Google FinanceGoogleticker symbol, [attribute], [start_date], [end_date|num_days], [interval]
HOURreturns a time’s hour value as a numberTime and Datetime
IMAGEinserts an image into a cellGoogleURL, mode, [height], [width]
IMPORTRANGEimports data from another spreadsheetWebspreadsheet URL, range string
IMPORTXMLimports XML data from the webWebURL, XPath_query, [locale]
INDEXretrieves a specified value by row and columnLookupreference, [row], [column]
ISOWEEKNUMreturns the ISO week number of a given dateTime and Datedate
MINUTEreturns a time’s minute value as a numberTime and Datetime
MONTHreturns a date’s month value as a numberTime and Datedate
NETWORKDAYScalculates the number of working days between two datesTime and Datestart date, end date, [holidays]
NETWORKDAYS.INTLcalculates the number of working days between two datesTime and Datestart date, end date, [holidays], [weekends]
NOWreturns the current date and timeTime and Datenone
QUERYuses SQL-like syntax to analyze data returned as a new tableGoogledata, query, [headers]
RANDgenerates a random decimalMathnone
RANDBETWEENgenerates a random integer between two given numbersMathlow, high
RANDARRAYgenerates an array of random decimalsMathrows, columns
SECONDreturns a time’s second value as a numberTime and Datetime
SLNreturns one period of straight-line depreciationFinancialcost, salvage value, life
SORTreturns a sorted tableFilterrange,sort_column,is_ascending,[sort_column2, is_acending2,…]
SORTNreturns n sorted lines from a table
Filterrange,[n],[display_ties_mode],[sort_column1, is_acending1],…
SPARKLINEinserts a miniature chart into a cellGoogledata, [options]
SUBTOTALperforms a variety of functions based on the function numberMathfunction number, numbers
SUMadds numbersMathnumbers
SUMIFadds numbers that meet a conditionMathnumbers, criterion, [criterion range]
SUMIFSadds numbers that meet multiple conditionsMathnumbers, criteria, [criteria range]
SYDreturns one period of accelerated depreciationFinancialcost, salvage value, life, period
TIMEturns three numbers into a timeTime and Datehour, minute, second
TIMEVALUEconverts a time string to a fractionTime and Datetime
TODAYreturns the current dateTime and Datenone
TYPEreturns an integer indicating the data typeInfovalue
UNIQUEreturns a table without duplicate linesFilterrange
WEEKDAYreturns the number of the of the weekTime and Datedate, [type]
WEEKNUMreturns the week number of a dayTime and Datedate, [type]
WORKDAYcalculates the workday a number of days awayTime and Datestart date, # of days, [holidays]
WORKDAY.INTLcalculates the workday a number of days awayTime and Datestart date, # of days, [holidays], [weekends]
YEARreturns the date’s year value as a numberTime and Datetime
YEARFRACcalculates the number of years, including fractions between two datesTime and Datestart date, end date, [day count convention]
XLOOKUPfind values in a range by rowLookuplookup, lookup_array, return_array, [not_found], [match_mode], [search_mode]
COUNTBLANKcounts the number of blank cellsMathvalues
IFevaluates a condition and returns a valueLogicalvalue, condition
IFSevaluates multiple conditions and returns a valueLogicalvalues, conditions
TEXTformats numerical dataTextvalue, format
CHARconverts a number to a characterTextnumber
UNICHARconverts a number to a characterTextnumber
CODEfinds a Unicode valueTexttext string
HEX2DECconverts a signed hexadecimal number to its decimal equivalentEngineeringvalue
INTrounds an integer down to the nearest whole numberMathnumber
UNICODEfinds a Unicode valueTexttext string
MODreturns the remainder of divisionMathnumbers
ROUNDrounds a number to a specified number of decimal placesMathvalue, [places]
ROUNDDOWNrounds a number down to a specified number of decimal placesMathvalue, [places]
ROUNDUProunds a number up to a specified number of decimal placesMathvalue, [places]
MROUNDrounds a number to the nearest multiple of a specified factorMathvalue, factor
CEILINGrounds a number up to the nearest integer multiple of a specified significanceMathvalue, factor
FLOORrounds a number down to the nearest integer multiple of a specified significanceMathvalue, factor
TRUNCremoves unwanted digits from a numberMathvalue, [places]
CONCATENATEcombines text stringsTexttext strings