Excel Functions

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

A cheatsheet by @miccohenRefreshed 10 months ago.

## Excel functions

### SUM

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

### MIN

Calculates the minimum value of a range of cells.

### MAX

Calculates the maximum value of a range of cells.

### AVERAGE

Calculates the average value of a range of cells.

### ROUND

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

### ROUNDUP

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

### ROUNDDOWN

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

### IF

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

### IFS

Returns a specified value if complex conditions have been met.

### COUNTIF

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

### COUNTIFS

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

### SUMIF

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

### SUMIFS

Sums the values in a range that meet multiple criteria.

### VLOOKUP

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

### 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

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

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

Calculates the total annual payment of a loan.

### IPMT

Calculates the interest component of a loan.

### PPMT

Calculates the principal component of a loan.

### 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.