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
CEILINGrounds a number up to the nearest specified multipleMathvalue, factor
CEILING.MATHrounds a number up to the nearest specified multipleMathnumber, significance, mode
CEILING.PRECISErounds a number up to the nearest specified multipleMathnumber, significance
CHARconverts a number to a characterTextnumber
CODEfinds a Unicode valueTexttext string
CONCATENATEcombines text stringsTexttext strings
COUNTcounts the numeric values in a data setStatisticalnumbers
COUNTAcounts the non-blank values in a data setStatisticalvalues
COUNTBLANKcounts the number of blank cellsMathvalues
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
DAYSreturns the number of days between two datesTime and Dateend date, start date
DAYS360returns the difference between two dates using the 360-day methodTime and Datestart date, end date, [method]
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…]
FLOORrounds a number down to the nearest specified multipleMathvalue, factor
FLOOR.MATHrounds a number down to the nearest specified multipleMathnumber, significance, mode
FLOOR.PRECISErounds a number down to the nearest specified multipleMathnumber, significance
GOOGLEFINANCEimports security data from Google FinanceGoogleticker symbol, [attribute], [start_date], [end_date|num_days], [interval]
HEX2DECconverts a signed hexadecimal number to its decimal equivalentEngineeringvalue
HOURreturns a time’s hour value as a numberTime and Datetime
IFevaluates a condition and returns a valueLogicalvalue, condition
IFSevaluates multiple conditions and returns a valueLogicalvalues, conditions
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]
INTrounds an integer down to the nearest whole numberMathnumber
ISDATEreturns TRUE if the input is a valid dateInfovalue
ISOWEEKNUMreturns the ISO week number of a given dateTime and Datedate
ISTEXTreturns TRUE if the input is a stringInfonumber
MAXreturns the largest valueStatisticalvalue
MAXAreturns the largest value treating text as a zeroStatisticalvalue
MINreturns the smallest valueStatisticalvalue
MINAreturns the smallest value treating text as a zeroStatisticalvalue
MINUTEreturns a time’s minute value as a numberTime and Datetime
MODreturns the remainder of divisionMathnumbers
MONTHreturns a date’s month value as a numberTime and Datedate
MROUNDrounds a number to the nearest multiple of a specified factorMathvalue, factor
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
RANDARRAYgenerates an array of random decimalsMathrows, columns
RANDBETWEENgenerates a random integer between two given numbersMathlow, high
RANKorders values in a data setStatisticalvalue, data, [is_ascending]
RANK.AVGorders values in a data set using average rankStatisticalvalue, data, [is_ascending]
RANK.EQorders values in a data setStatisticalvalue, data, [is_ascending]
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]
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
TEXTformats numerical dataTextvalue, format
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
TRAVELTIMEcalculates trip duration between locationsCustomorigin, destination, mode, departure time
TRAVELDIRECTIONSreturns turn-by-turn directionsCustomorigin, destination, unit, mode, departure time
TRAVELDISTANCEcalculates trip distance between locationsCustomorigin, destination, mode, unit
TRAVELROUTEURLreturns a link to Google Maps directionsCustomorigin, destination, mode
TRUNCremoves unwanted digits from a numberMathvalue, [places]
TYPEreturns an integer indicating the data typeInfovalue
UNICHARconverts a number to a characterTextnumber
UNICODEfinds a Unicode valueTexttext string
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]
XLOOKUPfind values in a range by rowLookuplookup, lookup_array, return_array, [not_found], [match_mode], [search_mode]
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]