Question
Answer
=SUM(range)
Adds up all numbers in a range. Example: =SUM(A1:A10)
=AVERAGE(range)
Calculates the average of a range. Example: =AVERAGE(B2:B20)
=COUNT(range)
Counts how many cells contain numbers. Example: =COUNT(A1:A10)
=COUNTA(range)
Counts how many cells are not empty. Example: =COUNTA(A1:A10)
=COUNTIF(range, criteria)
Counts cells that meet a condition. Example: =COUNTIF(A1:A10,">5")
=SUMIF(range, criteria, sum_range)
Adds values that meet a condition. Example: =SUMIF(A1:A10,">5",B1:B10)
=IF(condition, value_if_true, value_if_false)
Returns one value if true, another if false. Example: =IF(A1>10,"Pass","Fail")
=VLOOKUP(value, table, col_index, false)
Looks up a value in the first column and returns a value from another column. Example: =VLOOKUP(A2,D:F,3,FALSE)
=INDEX(range, row, col)
Returns the value at a specific row/column position. Example: =INDEX(A1:C10,2,3)
=MATCH(value, range, 0)
Finds the position of a value in a range. Example: =MATCH("Apple",A1:A10,0)
=CONCATENATE(text1, text2)
Joins text together. Example: =CONCATENATE(A1," ",B1)
=TEXT(value, format)
Formats a number as text in a specific style. Example: =TEXT(A1,"$0.00")
=TRIM(text)
Removes extra spaces from text. Example: =TRIM(A1)
=LEN(text)
Counts the number of characters in text. Example: =LEN(A1)
=LEFT(text, num)
Returns characters from the start of text. Example: =LEFT(A1,3)
=RIGHT(text, num)
Returns characters from the end of text. Example: =RIGHT(A1,3)
=MID(text, start, num)
Returns characters from the middle of text. Example: =MID(A1,2,5)
=ROUND(number, digits)
Rounds a number to a specified number of decimal places. Example: =ROUND(A1,2)
=TODAY()
Returns today's date. Example: =TODAY()
=NOW()
Returns the current date and time. Example: =NOW()
=MAX(range)
Returns the largest number in a range. Example: =MAX(A1:A10)
=MIN(range)
Returns the smallest number in a range. Example: =MIN(A1:A10)
=AND(condition1, condition2)
Returns TRUE only if all conditions are true. Example: =AND(A1>0,B1>0)
=OR(condition1, condition2)
Returns TRUE if any condition is true. Example: =OR(A1>0,B1>0)
=NOT(condition)
Reverses TRUE/FALSE. Example: =NOT(A1>0)
=ISBLANK(cell)
Checks if a cell is empty. Example: =ISBLANK(A1)
=IFERROR(value, value_if_error)
Returns a custom result if a formula errors. Example: =IFERROR(A1/B1,"Error")
=SUMPRODUCT(range1, range2)
Multiplies matching items and adds the results. Example: =SUMPRODUCT(A1:A5,B1:B5)
=UNIQUE(range)
Returns a list of unique values. Example: =UNIQUE(A1:A10)
=FILTER(range, condition)
Returns only rows that match a condition. Example: =FILTER(A1:B10,B1:B10>5)
=SORT(range, col, ascending)
Sorts a range by a column. Example: =SORT(A1:B10,2,TRUE)
=QUERY(range, query)
Runs a SQL-like query on data (Google Sheets). Example: =QUERY(A1:C10,"SELECT A WHERE C>5")
=ARRAYFORMULA(formula)
Applies a formula to an entire range at once (Google Sheets). Example: =ARRAYFORMULA(A1:A10*2)
$A$1 (absolute reference)
Locks a cell reference so it doesn't change when copied. Example: =A1*$B$1
=PMT(rate, nper, pv)
Calculates a loan payment. Example: =PMT(0.05/12,60,10000)
=NPV(rate, values)
Calculates the net present value of a series of cash flows. Example: =NPV(0.1,A1:A5)
=COUNTIFS(range1,crit1,range2,crit2)
Counts cells meeting multiple conditions. Example: =COUNTIFS(A:A,"Yes",B:B,">10")
=SUMIFS(sum_range,range1,crit1,range2,crit2)
Adds values meeting multiple conditions. Example: =SUMIFS(C:C,A:A,"Yes",B:B,">10")
=XLOOKUP(value, lookup_range, return_range)
Modern replacement for VLOOKUP that can search in any direction. Example: =XLOOKUP(A2,D:D,F:F)
Create a free account to track your progress and save this deck.
Sign up free →