Excel Formulas

Count

Formula	Related Functions
Count unique numeric values in a range	SUM COUNTIF FREQUENCY
Count unique numeric values with criteria	SUM FREQUENCY
Count unique text values in a range	MATCH ROW SUMPRODUCT FREQUENCY
Count unique text values with criteria	MATCH ROW SUM FREQUENCY
Count unique values in a range with COUNTIF	SUMPRODUCT COUNTIF
Count visible rows in a filtered list	SUBTOTAL
Count visible rows only with criteria
COUNTIFS with multiple criteria and OR logic	COUNTIFS
Running count of occurrence in list	COUNTIF
Summary count with COUNTIF	COUNTIF

Sum

Formula	Related Functions
Sum if date is greater than	DATE SUMIF
Sum if ends with	SUMIF
Sum if equal to either x or y	SUMIF SUMPRODUCT
Sum if greater than	SUMIF
Sum if less than	SUMIF
Sum if multiple criteria
Sum if cells not blank	SUMIF
Sum top n values	INDIRECT ROW SUM SUMPRODUCT LARGE
Sum visible rows in a filtered list	SUBTOTAL
SUMIFS with multiple criteria and OR logic	SUM

Average

Formula	Related Functions
Average numbers	AVERAGE
Average numbers ignore zero	AVERAGEIF
Average top 3 scores	AVERAGE LARGE
Weighted average	SUM SUMPRODUCT

Min and Max

Formula	Related Functions
Maximum if multiple criteria	IF MIN
Maximum value
Maximum value if	IF
Minimum if multiple criteria	IF MIN
Minimum value	MIN
Minimum value if	IF MIN
nth largest value	LARGE
nth largest value with criteria	LARGE
nth smallest value	SMALL
nth smallest value with criteria	SMALL

Conditional Formatting

Formula	Related Functions
Highlight entire rows
Highlight every other row	ISEVEN ISODD ROW MOD
Highlight integers only	MOD
Highlight rows that contain specific text	FIND SEARCH
Highlight rows with dates between	DATE AND
Highlight unique values	COUNTIF
Highlight values between	AND
Highlight values greater than
Highlight values not between X and Y	AND NOT
Highlight values that don’t exist	COUNTIF

Lookup

Formula	Related Functions
Position of max value in list	MATCH
Related info for max value in range	INDEX MATCH
Self-contained VLOOKUP	VLOOKUP
Two-way lookup with INDEX and MATCH	INDEX MATCH
Two-way lookup with VLOOKUP	MATCH VLOOKUP
VLOOKUP with 2 lookup tables	VLOOKUP
VLOOKUP with calculated column index	VLOOKUP
VLOOKUP with numbers and text	VLOOKUP
VLOOKUP with two or more criteria	VLOOKUP
VLOOKUP without #N/A error	IFERROR VLOOKUP

If

Formula	Related Functions
If cell equals	IF
If cell is blank	ISBLANK IF
If cell is greater than	IF
If cell is not blank	ISBLANK IF NOT
If cell is this OR that	IF OR
If cell is x or y and z	AND IF OR
If else	IF
If NOT this or that	IF NOT OR
If this AND that	AND IF
Nested IF function example	IF

Round

Formula	Related Functions
Round a number down to nearest multiple	FLOOR
Round a number to n significant digits	ABS INT LOG10 ROUND
Round a number to nearest multiple	MROUND
Round a number up	ROUNDUP
Round a number up to nearest multiple	CEILING
Round a number up to next half	CEILING
Round a price to end in .99	ROUND
Round time to nearest 15 minutes	MROUND
Round to nearest 1000	ROUND
Round to nearest 5	CEILING FLOOR MROUND

Date And Time

Formula	Related Functions
Get workdays between dates	NETWORKDAYS
Get year from date	YEAR
Group times into 3 hour buckets	FLOOR
Group times into unequal buckets	VLOOKUP
Next business day 6 months in future	WORKDAY
Pad week numbers with zeros	WEEKNUM TEXT
Sum race time splits	SUM
Total hours that fall between two times	MIN
Workdays per month	EOMONTH NETWORKDAYS
Year is a leap year	DATE MONTH YEAR

Date Series

Formula	Related Functions
Series of dates by day
Series of dates by month	DATE DAY EOMONTH MONTH YEAR
Series of dates by weekends	WEEKDAY IF
Series of dates by workdays	WEEKDAY WORKDAY IF
Series of dates by year	DATE DAY MONTH YEAR

Text

Finalcial

Formula	Related Functions
Calculate compound interest	FV
Calculate simple interest

Workbook

Internet

Formula	Related Functions
Get domain from email address	FIND LEN RIGHT
Get domain name from URL	FIND LEFT
Get name from email address	FIND LEFT
Get top level domain (TLD)	FIND LEN RIGHT SUBSTITUTE

Names

Formula	Related Functions
Get first name from Last, First	FIND LEN RIGHT
Get first name from name	FIND LEFT
Get last name from Last, First	LEFT LEN
Get last name from name	FIND LEN RIGHT SUBSTITUTE
Get middle name from full name	LEN MID TRIM
Join first and last name	CONCATENATE
Put names into proper case	PROPER TRIM

Percentage

Formula	Related Functions
Get amount with percentage
Get original price from percentage discount
Get percent change
Get percentage discount
Get percentage of total
Get profit margin percentage
Get total from percentage
Increase by percentage
Percent of goal
Percent of students absent

Range

Formula	Related Functions
First row number in range	ROW MIN
Get relative column numbers in range	COLUMN
Get relative row numbers in range	ROW
Last column number in range	COLUMN COLUMNS MIN
Last row number in range	ROW ROWS MIN
Range contains a value not in another range	ISNA MATCH SUMPRODUCT
Range contains numbers	ISNUMBER SUMPRODUCT
Total cells in a range	COLUMNS ROWS
Total columns in range	COLUMNS
Total rows in range	ROWS

Miscellaneous

Formula	Related Functions
Random date between two dates	WORKDAY RANDBETWEEN
Random number between two numbers	RANDBETWEEN
Random text values	CHOOSE RANDBETWEEN
Randomly assign data to groups	CHOOSE RANDBETWEEN
Range contains one of many substrings	ISNUMBER SUMPRODUCT SEARCH
Range contains one of many values	ISNUMBER SUMPRODUCT SEARCH
Range contains specific text	COUNTIF
Range contains specific value	MATCH COUNTIF
Reverse a list or range	INDEX ROW COUNTA
Transpose table without zeros	IF TRANSPOSE