Cheat sheet

Every formula on one page, with what each argument is actually for. Generated from the lessons, so it cannot drift out of date.

SUM

Open the lesson

Add up a column of numbers

=SUM(what to add up)

number1what to add up

AVERAGE

Open the lesson

Find the middle of a set of numbers

=AVERAGE(what to average)

number1what to average

COUNT and COUNTA

Open the lesson

Count numbers, or count anything at all

=COUNT(where to count numbers)

=COUNTA(where to count anything)

value1where to count numbers
value1where to count anything

IF

Open the lesson

Do one thing when true, another when false

=IF(what to check, if it is true, if it is false, optional)

logical_testwhat to check
value_if_trueif it is true
[value_if_false]if it is false

COUNTIF

Open the lesson

Count only the rows that meet a condition

=COUNTIF(where to look, what counts)

rangewhere to look
criteriawhat counts

SUMIF

Open the lesson

Add up only the rows that meet a condition

=SUMIF(where to check, what counts, what to add up, optional)

rangewhere to check
criteriawhat counts
[sum_range]what to add up

COUNTIFS

Open the lesson

Count rows that meet several conditions at once

=COUNTIFS(first column to check, first condition, next column, optional, next condition, optional)

criteria_range1first column to check
criteria1first condition
[criteria_range2]next column
[criteria2]next condition

SUMIFS

Open the lesson

Add up rows that meet several conditions at once

=SUMIFS(what to add up, first column to check, first condition, next column, optional, next condition, optional)

sum_rangewhat to add up
criteria_range1first column to check
criteria1first condition
[criteria_range2]next column
[criteria2]next condition

VLOOKUP

Open the lesson

The older lookup you will still meet in real files

=VLOOKUP(who to find, the whole table, which column, counting from the left, FALSE for an exact match, optional)

lookup_valuewho to find
table_arraythe whole table
col_index_numwhich column, counting from the left
[range_lookup]FALSE for an exact match

XLOOKUP

Open the lesson

Find a row by one value, bring back another

=XLOOKUP(who to find, where to look, what to bring back, what to show instead, optional)

lookup_valuewho to find
lookup_arraywhere to look
return_arraywhat to bring back
[if_not_found]what to show instead

MIN and MAX

Open the lesson

Find the smallest and largest numbers in a range

=MIN(where to find the smallest number)

=MAX(where to find the largest number)

number1where to find the smallest number
number1where to find the largest number

ROUND

Open the lesson

Control how many decimal places a result keeps

=ROUND(what to round, decimal places to keep)

numberwhat to round
num_digitsdecimal places to keep

AND and OR

Open the lesson

Test whether every condition or any condition is true

=AND(first condition, next condition, optional)

=OR(first condition, next condition, optional)

logical1first condition
[logical2]next condition
logical1first condition
[logical2]next condition

IFERROR

Open the lesson

Replace formula errors with a useful result

=IFERROR(formula to try, what to show if it fails)

valueformula to try
value_if_errorwhat to show if it fails

LEFT, RIGHT and MID

Open the lesson

Extract a useful part from a text code

=LEFT(text to cut, characters from the left, optional)

=RIGHT(text to cut, characters from the right, optional)

=MID(text to cut, where to start, characters to take)

texttext to cut
[num_chars]characters from the left
texttext to cut
[num_chars]characters from the right
texttext to cut
start_numwhere to start
num_charscharacters to take

TRIM and LEN

Open the lesson

Clean stray spaces and measure text length

=TRIM(text to clean)

=LEN(text to measure)

texttext to clean
texttext to measure

CONCAT

Open the lesson

Join text and cell values into one result

=CONCAT(first text, next text, optional)

text1first text
[text2]next text

INDEX and MATCH

Open the lesson

Find a position, then return the value at that position

=INDEX(where the answer lives, which row in that range, which column in that range, optional)

=MATCH(what to find, where to look, 0 for an exact match, optional)

arraywhere the answer lives
row_numwhich row in that range
[column_num]which column in that range
lookup_valuewhat to find
lookup_arraywhere to look
[match_type]0 for an exact match