MS EXCEL FORMULAS

Purpose /
Formula Syntax
Description

Adds all numbers in

SUM =SUM(range)
the specified range.

Adds cells that meet

SUMIF =SUMIF(range, criteria, [sum_range]) one specific
condition.

=SUMIFS(sum_range,
Adds cells that meet
SUMIFS criteria_range1, criteria1,
multiple conditions.
[criteria_range2, criteria2], …)

Counts cells that

COUNT =COUNT(range)
contain numbers.

Counts all non-

COUNTA =COUNTA(range) empty cells
(numbers + text).

Counts cells that

COUNTIF =COUNTIF(range, criteria)
meet one condition.

=COUNTIFS(criteria_range1, Counts cells that

COUNTIFS criteria1, [criteria_range2, criteria2], meet multiple
…) conditions.

Finds the mean

AVERAGE =AVERAGE(range) (average) of
numbers.
 Purpose /
Formula Syntax
Description

=AVERAGEIF(range, criteria, Averages cells that

AVERAGEIF
[average_range]) meet one condition.

=AVERAGEIFS(average_range, Averages cells that

AVERAGEIFS criteria_range1, criteria1, meet multiple
[criteria_range2, criteria2], …) conditions.

Returns the largest

MAX =MAX(range)
number in a range.

Returns the smallest

MIN =MIN(range)
number in a range.

Checks a condition
=IF(logical_test, value_if_true, and returns one
IF
value_if_false) value if true, another
if false.

Tests multiple
=IFS(condition1, value1, condition2,
IFS conditions without
value2, …)
nesting IFs.

Returns TRUE if all

AND =AND(logical1, logical2, …)
conditions are TRUE.

Returns TRUE if any

OR =OR(logical1, logical2, …)
condition is TRUE.

Reverses the result of

NOT =NOT(logical)
a logical test.
 Purpose /
Formula Syntax
Description

=VLOOKUP(lookup_value,
Searches for a value
VLOOKUP table_array, col_index_num,
vertically in a table.
[range_lookup])

=HLOOKUP(lookup_value, Searches for a value

HLOOKUP table_array, row_index_num, horizontally in a
[range_lookup]) table.

Modern, flexible
=XLOOKUP(lookup_value,
lookup replacing
XLOOKUP lookup_array, return_array,
VLOOKUP &
[if_not_found])
HLOOKUP.

Returns a value from

=INDEX(array, row_num,
INDEX a given position in a
[column_num])
range.

=MATCH(lookup_value, Finds the position of

MATCH
lookup_array, [match_type]) a value in a range.

Extracts characters
LEFT =LEFT(text, num_chars) from the left side of
text.

Extracts characters
RIGHT =RIGHT(text, num_chars) from the right side of
text.

Extracts characters
MID =MID(text, start_num, num_chars) from the middle of
text.
 Purpose /
Formula Syntax
Description

Removes extra
TRIM =TRIM(text)
spaces from text.

Capitalizes the first

PROPER =PROPER(text)
letter of each word.

Converts text to
UPPER =UPPER(text)
uppercase.

Converts text to
LOWER =LOWER(text)
lowercase.

CONCATENATE / =CONCATENATE(text1, text2, …) or Joins (combines) text

CONCAT =CONCAT(text1, text2, …) from multiple cells.

Returns the current

TODAY =TODAY()
date.

Returns the current

NOW =NOW()
date and time.

Returns a custom
IFERROR =IFERROR(value, value_if_error) message/value if
there’s an error.