491 spreadsheet functions, on live data
Sheets evaluates the standard spreadsheet functions with the usual names and results: lookups, conditional sums, dynamic arrays that spill, LET and LAMBDA. They work on cells and on data read live from the tools you connect.
With worked examples
ANDTRUE if all are TRUEAVERAGEMean of valuesAVERAGEIFMean where one criterion matchesAVERAGEIFSConditional meanCONCATJoin text stringsCOUNTCount numbersCOUNTACount non-emptyCOUNTIFCount where one criterion matchesCOUNTIFSConditional countDATEDate from parts (yyyy-mm-dd)EOMONTHLast day of the month N months awayFILTERRows or columns that meet a conditionIFConditionalIFERRORFallback if value errorsIFSFirst value whose condition is TRUEINDEXValue at a row/column of a rangeLEFTFirst characters of textLENString lengthLETName values for a calculationLOWERLowercaseMATCHPosition of a value in a rangeMAXMaximum valueMAXIFSLargest value where all criteria matchMEDIANMiddle valueMINMinimum valueMINIFSSmallest value where all criteria matchMONTHMonth of a date (1-12)NETWORKDAYSWorking days between two datesORTRUE if any is TRUEPERCENTILE.INCThe k-th percentile, k from 0 to 1PMTPeriodic payment of a loan or annuityPROPERCapitalize each wordROUNDRound to N decimalsSORTSort the rows or columns of an arraySUBSTITUTEReplace textSUMSum of valuesSUMIFSum where one criterion matchesSUMIFSConditional sumSUMPRODUCTSum of element-wise productsSWITCHResult for the matching valueTEXTFormat a number or date as textTEXTJOINJoin texts with a delimiterTRIMRemove extra spacesUNIQUEDistinct rows or columns of an arrayVLOOKUPLook up down the first columnXLOOKUPLook up a value, return the matching itemXMATCHPosition of an item in a row or columnYEARYear of a date
By category
Math & trig84
ABS, ACOS, ACOSH, ACOT, ACOTH, AGGREGATE…Statistical147
AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, BETA.DIST…Lookup & reference37
ADDRESS, AREAS, CHOOSE, CHOOSECOLS, CHOOSEROWS, COLUMN…Logical20
AND, BYCOL, BYROW, FALSE, IF, IFERROR…Text48
ARRAYTOTEXT, ASC, BAHTTEXT, CHAR, CLEAN, CODE…Date & time25
DATE, DATEDIF, DATEVALUE, DAY, DAYS, DAYS360…Financial55
ACCRINT, ACCRINTM, AMORDEGRC, AMORLINC, COUPDAYBS, COUPDAYS…Information21
CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR…Engineering54
BESSELI, BESSELJ, BESSELK, BESSELY, BIN2DEC, BIN2HEX…Formulas that read your tools live
Connect your accounting, store or boards and use these functions on their data.