Formulas
Spreadsheet formulas assembled from your own cell references — XLOOKUP, SUMIFS and friends, each explained with its Excel-vs-Sheets fine print.
Formula4 blanks
Look up a value, with a fallback=XLOOKUP([the value to find (cell)], [column to search in], [column to return from], [what to show when not found])
Formula4 blanks
Sum between two dates=SUMIFS([numbers to add up], [the date column], ">="&[start date (cell)], [the date column], "<="&[end date (cell)])
Formula3 blanks
Look up a value with INDEX and MATCH=INDEX([column to return from], MATCH([the value to find (cell)], [column to search in], 0))
Formula4 blanks
Count rows that match several conditions=COUNTIFS([first column to check], [condition for that column], [second column to check], [condition for that column])
Formula3 blanks
Average values that meet a condition=IFERROR(AVERAGEIFS([numbers to average], [column to filter on], [condition to match]), "")
Formula2 blanks
Wrap a formula with a friendly fallback=IFERROR([the formula to protect], [what to show on any error])
Formula1 blanks
Pull the last name out of a full-name cell=RIGHT([the full-name cell], LEN([the full-name cell]) - FIND(" ", [the full-name cell]))
Formula3 blanks
Join several cells with a separator=TEXTJOIN([separator between values], [skip blank cells? (true/false)], [cells to join])
Formula1 blanks
Clean up text pasted from the web=TRIM(CLEAN(SUBSTITUTE([the pasted-in cell], CHAR(160), " ")))
Formula2 blanks
Calculate years and months of tenure between two dates=DATEDIF([start date (cell)], [end date (cell, or today())], "y") & " years, " & DATEDIF([start date (cell)], [end date (cell, or today())], "ym") & " months"
Formula2 blanks
Calculate a due date N months from a start date=EOMONTH([start date (cell)], [months forward (negative for back)])
Formula3 blanks
Count working days between two dates=NETWORKDAYS([start date (cell)], [end date (cell)], [holiday dates to exclude (range)])
Formula2 blanks
Calculate percent change between two values=IF([original value (cell)]=0, "", ([new value (cell)]-[original value (cell)])/[original value (cell)])
Formula2 blanks
Build a running total down a column=SUM([first cell of the column (anchored)]:[this row's cell])
Formula5 blanks
Test whether a value falls between two numbers=IF(AND([value to test (cell)]>=[lower bound], [value to test (cell)]<=[upper bound]), [result when in range], [result when out of range])