Excel Functions

Excel functions cheatsheet - sum, min, max, roundup, if, rounddown, sumif, vlookup, hlookup, countif, index, match and more

A cheatsheet by @miccohen|Refreshed 7 months ago.Refresh|View source on Github

SUM

SUM

Adds up, or sums together, a range of cells.

MIN

MIN

Calculates the minimum value of a range of cells.

MAX

MAX

Calculates the maximum value of a range of cells.

AVERAGE

AVERAGE

Calculates the average value of a range of cells.

ROUND

ROUND

Rounds a single number to the nearest specified value, usually to a whole number.

ROUNDUP

ROUNDUP

Rounds up a single number to the nearest specified value, usually to a whole number.

ROUNDDOWN

ROUNDDOWN

Rounds down a single number to the nearest specified value, usually to a whole number.

IF

IF

Returns a specified value only if a single condition has been met.

IFS

IFS

Returns a specified value if complex conditions have been met.

COUNTIF

COUNTIF

Counts the number of values in a range that meet a certain single criterion.

COUNTIFS

COUNTIFS

Counts the number of values in a range that meet multiple criteria.

SUMIF

SUMIF

Sums the values in a range that meet a certain single criterion.

SUMIFS

SUMIFS

Sums the values in a range that meet multiple criteria.

VLOOKUP

VLOOKUP

Looks up a range and returns the first corresponding value in a vertical table that matches exactly the specified input.

HLOOKUP

HLOOKUP

Looks up a range and returns the first corresponding value in a horizontal table that matches exactly the specified input. An error is returned if it cannot find the exact match.

INDEX

INDEX

Works like the coordinates of a map and returns a single value based on the column and row numbers you input into the function fields.

MATCH

MATCH

Returns the position of a value in a column or a row. Modelers often combine MATCH with the INDEX function to create a lookup function, which is far more robust and flexible and uses less memory than the VLOOKUP or HLOOKUP.

PMT

PMT

Calculates the total annual payment of a loan.

IPMT

IPMT

Calculates the interest component of a loan.

PPMT

PPMT

Calculates the principal component of a loan.

NPV

NPV

Takes into account the time value of money by giving the net present value of future cash flows in today’s dollars, based on the investment amount and discount rate.