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
CEILING.MATHrounds a number up to the nearest specified multipleMathnumber, significance, mode
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
ISDATEreturns TRUE if the input is a valid dateInfovalue
ISTEXTreturns TRUE if the input is a stringInfonumber
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 specified multipleMathvalue, factor
CEILING.PRECISErounds a number up to the nearest specified multipleMathnumber, significance
FLOOR.MATHrounds a number down to the nearest specified multipleMathnumber, significance, mode
FLOORrounds a number down to the nearest specified multipleMathvalue, factor
FLOOR.PRECISErounds a number down to the nearest specified multipleMathnumber, significance
RANKorders values in a data setStatisticalvalue, data, [is_ascending]
RANK.EQorders values in a data setStatisticalvalue, data, [is_ascending]
RANK.AVGorders values in a data set using average rankStatisticalvalue, data, [is_ascending]
TRUNCremoves unwanted digits from a numberMathvalue, [places]
CONCATENATEcombines text stringsTexttext strings
MAXreturns the largest valueStatisticalvalue
MINreturns the smallest valueStatisticalvalue