GROUP 01
Add and count
SUM
Adds numbers, cells, or ranges.
SUM(number1, [number2], …)=SUM(B2:B20)SUMIF
Adds values when one range meets one condition.
SUMIF(range, criteria, [sum_range])=SUMIF(A2:A100,"West",C2:C100)SUMIFS
Adds values when several range-and-condition pairs are all satisfied.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)=SUMIFS(D:D,B:B,"West",C:C,">=100")SUMIFS puts the sum range first; SUMIF puts it last.
COUNTIF
Counts cells in one range that meet one condition.
COUNTIF(range, criteria)=COUNTIF(C2:C100,"Late")COUNTIFS
Counts rows that satisfy several range-and-condition pairs.
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)=COUNTIFS(B:B,"West",C:C,">=100")AVERAGE
Calculates the arithmetic mean of numeric values.
AVERAGE(number1, [number2], …)=AVERAGE(B2:B20)GROUP 02
Look up
XLOOKUP
Finds a value in one range and returns the corresponding value from another.
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])=XLOOKUP(E2,A2:A100,C2:C100,"Not found")XLOOKUP is unavailable in older perpetual Excel versions.
VLOOKUP
Looks down the first column of a table and returns a value to its right.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=VLOOKUP(E2,A2:C100,3,FALSE)Use FALSE for an exact match. VLOOKUP cannot return a column to the left.
INDEX
Returns the value at a given row and optional column in a range.
INDEX(array, row_num, [column_num])=INDEX(B2:B100,MATCH(E2,A2:A100,0))MATCH
Returns the position of a lookup value inside one row or column.
MATCH(lookup_value, lookup_array, [match_type])=MATCH(E2,A2:A100,0)Use 0 for an exact match unless the lookup array is intentionally sorted.
FILTER
Returns only rows or columns that meet a Boolean condition.
FILTER(array, include, [if_empty])=FILTER(A2:D100,D2:D100="Open","No rows")FILTER requires a dynamic-array version of Excel.
GROUP 03
Decide and recover
IF
Returns one value when a test is true and another when it is false.
IF(logical_test, value_if_true, value_if_false)=IF(C2>=70,"Pass","Review")IFERROR
Returns a fallback when the wrapped expression produces an error.
IFERROR(value, value_if_error)=IFERROR(A2/B2,"Check divisor")A broad IFERROR can hide a data-quality problem; use a specific fallback.
ISBLANK
Returns TRUE only when a referenced cell is genuinely empty.
ISBLANK(value)=ISBLANK(A2)A cell containing a formula that returns "" is not genuinely empty.
ISERROR
Returns TRUE when an expression produces any Excel error.
ISERROR(value)=ISERROR(A2/B2)AND
Returns TRUE only when every supplied condition is true.
AND(logical1, [logical2], …)=AND(B2>=70,C2="Complete")OR
Returns TRUE when at least one supplied condition is true.
OR(logical1, [logical2], …)=OR(C2="Late",D2="Escalated")LET
Names intermediate values so a long formula is easier to read and calculate.
LET(name1, value1, calculation)=LET(total,SUM(B2:B20),total/COUNTA(B2:B20))LET is unavailable in older perpetual Excel versions.
GROUP 04
Clean and present
LEFT
Returns characters from the beginning of a text value.
LEFT(text, [num_chars])=LEFT(A2,3)RIGHT
Returns characters from the end of a text value.
RIGHT(text, [num_chars])=RIGHT(A2,4)MID
Returns characters from the middle of a text value.
MID(text, start_num, num_chars)=MID(A2,5,8)FIND
Returns the case-sensitive position of one text value inside another.
FIND(find_text, within_text, [start_num])=FIND("-",A2)FIND returns #VALUE! when the text is not present and is case-sensitive.
TEXTBEFORE
Returns the text that appears before a chosen delimiter.
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])=TEXTBEFORE(A2,"-")TEXTBEFORE is unavailable in older perpetual Excel versions.
TEXT
Formats a number or date as text using a format code.
TEXT(value, format_text)=TEXT(A2,"mmm d, yyyy")TEXTJOIN
Joins text with a delimiter and can ignore empty cells.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)=TEXTJOIN(", ",TRUE,A2:C2)CONCAT
Joins text values without adding a delimiter.
CONCAT(text1, [text2], …)=CONCAT(A2," ",B2)ROUND
Rounds a number to a specified number of digits.
ROUND(number, num_digits)=ROUND(B2*C2,2)GROUP 05
Dynamic lists and dates
UNIQUE
Returns distinct rows or columns from a range.
UNIQUE(array, [by_col], [exactly_once])=UNIQUE(A2:A100)UNIQUE requires a dynamic-array version of Excel.
SORT
Returns a sorted copy of a range or array.
SORT(array, [sort_index], [sort_order], [by_col])=SORT(A2:D100,4,-1)SORT requires a dynamic-array version of Excel.
TAKE
Returns a chosen number of rows or columns from the start or end of an array.
TAKE(array, rows, [columns])=TAKE(A2:D100,-5)TAKE requires a current Microsoft 365 version of Excel.
DATE
Builds a real Excel date from year, month, and day numbers.
DATE(year, month, day)=DATE(2026,7,27)TODAY
Returns the current date according to the workbook environment.
TODAY()=TODAY()TODAY is volatile and recalculates when the workbook recalculates.