Excel Formulas Cheat Sheet

Master Excel with our comprehensive formulas reference guide. From basic math to advanced lookup functions, find syntax, examples, and expert tips for all essential Excel formulas.

Found 45 formulas matching "IF"

Excel Mastery Guide

Beginner

Start with basic calculations and data organization

• SUM, AVERAGE, COUNT
• Basic IF statements
• Text functions (CONCATENATE, LEN)
• TODAY, NOW functions

Intermediate

Master lookup functions and data analysis

• VLOOKUP, HLOOKUP
• Nested IF statements
• AND, OR functions
• Date calculations

Advanced

Advanced functions and financial modeling

• INDEX-MATCH combinations
• Array formulas
• Financial functions (PV, FV, PMT)
• Statistical analysis

Excel Pro Tips

F4 Key: Toggle between absolute and relative references ($A$1, A$1, $A1, A1)

Ctrl + Shift + Enter: Enter array formulas (older Excel versions)

F9 Key: Calculate formulas manually when set to manual calculation mode

Ctrl + `: Toggle between formula view and result view

MINIFS()

Math & Statistical

Returns minimum value among cells specified by criteria

Syntax:

=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Examples:

=MINIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100")

Minimum C where A='Apple' AND B>100

=MINIFS(D1:D10, A1:A10, "North")

Minimum D for North region

=MINIFS(C1:C10, A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1))

Minimum for dates in 2024

Note: Available in Excel 2016 and later

MAXIFS()

Math & Statistical

Returns maximum value among cells specified by criteria

Syntax:

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Examples:

=MAXIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100")

Maximum C where A='Apple' AND B>100

=MAXIFS(D1:D10, A1:A10, "North")

Maximum D for North region

=MAXIFS(C1:C10, A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1))

Maximum for dates in 2024

Note: Available in Excel 2016 and later

DATEDIF()

Date & Time

Calculates difference between two dates

Syntax:

=DATEDIF(start_date, end_date, unit)

Examples:

=DATEDIF(A1, B1, "Y")

Years between dates

=DATEDIF(A1, B1, "M")

Months between dates

=DATEDIF(A1, B1, "D")

Days between dates

Note: Units: Y=years, M=months, D=days, MD=days ignoring months/years, YM=months ignoring years, YD=days ignoring years

IF()

Logical Functions

Returns different values based on condition

Syntax:

=IF(logical_test, value_if_true, value_if_false)

Examples:

=IF(A1>100, "High", "Low")

Return text based on value

=IF(B1="", "Empty", B1)

Handle empty cells

=IF(A1>B1, A1, B1)

Return larger of two values

Note: Can be nested for multiple conditions

SUMIF()

Math & Statistical

Sums cells that meet a criteria

Syntax:

=SUMIF(range, criteria, [sum_range])

Examples:

=SUMIF(A1:A10, ">100")

Sum values greater than 100

=SUMIF(A1:A10, "Apple", B1:B10)

Sum B values where A contains 'Apple'

=SUMIF(A1:A10, A12, B1:B10)

Sum B values where A equals value in A12

Note: If sum_range is omitted, range is used for both criteria and sum

AVERAGEIF()

Math & Statistical

Averages cells that meet a criteria

Syntax:

=AVERAGEIF(range, criteria, [average_range])

Examples:

=AVERAGEIF(A1:A10, ">100")

Average of values greater than 100

=AVERAGEIF(A1:A10, "Apple", B1:B10)

Average B values where A contains 'Apple'

=AVERAGEIF(A1:A10, A12, B1:B10)

Average B values where A equals value in A12

Note: If average_range is omitted, range is used for both criteria and average

SUMIFS()

Math & Statistical

Sums cells that meet multiple criteria

Syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Examples:

=SUMIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100")

Sum C where A='Apple' AND B>100

=SUMIFS(D1:D10, A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1))

Sum D for dates in 2024

=SUMIFS(C1:C10, A1:A10, "North", B1:B10, "Q1")

Sum for North region in Q1

Note: All criteria must be met for a cell to be included in sum

COUNTIF()

Math & Statistical

Counts cells that meet a criteria

Syntax:

=COUNTIF(range, criteria)

Examples:

=COUNTIF(A1:A10, ">100")

Count cells greater than 100

=COUNTIF(A1:A10, "Apple")

Count cells containing 'Apple'

=COUNTIF(A1:A10, A12)

Count cells equal to value in A12

Note: Supports wildcards: * for multiple characters, ? for single character

COUNTIFS()

Math & Statistical

Counts cells that meet multiple criteria

Syntax:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Examples:

=COUNTIFS(A1:A10, "Apple", B1:B10, ">100")

Count where A='Apple' AND B>100

=COUNTIFS(A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1))

Count dates in 2024

=COUNTIFS(B1:B10, "North", C1:C10, "Q1")

Count North region in Q1

Note: All criteria must be met for a cell to be counted

AVERAGEIFS()

Math & Statistical

Averages cells that meet multiple criteria

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Examples:

=AVERAGEIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100")

Average C where A='Apple' AND B>100

=AVERAGEIFS(D1:D10, A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1))

Average D for dates in 2024

=AVERAGEIFS(C1:C10, A1:A10, "North", B1:B10, "Q1")

Average for North region in Q1

Note: All criteria must be met for a cell to be included in average

IFS()

Logical Functions

Tests multiple conditions and returns first TRUE result

Syntax:

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Examples:

=IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F")

Grade based on score

=IFS(B1="Red", 1, B1="Blue", 2, B1="Green", 3, TRUE, 0)

Assign numbers to colors

=IFS(A1<0, "Negative", A1=0, "Zero", A1>0, "Positive")

Check number sign

Note: Available in Excel 2016 and later. Use TRUE as final condition for default value

ISERROR()

Information Functions

Tests if value is an error

Syntax:

=ISERROR(value)

Examples:

=ISERROR(A1/B1)

TRUE if division results in error

=IF(ISERROR(VLOOKUP(A1, B:C, 2, FALSE)), "Not Found", VLOOKUP(A1, B:C, 2, FALSE))

Handle VLOOKUP errors

=IFERROR(A1/B1, "Division Error")

Simpler error handling

Note: Use IFERROR() for simpler error handling

ISNA()

Information Functions

Tests if value is #N/A error

Syntax:

=ISNA(value)

Examples:

=ISNA(VLOOKUP(A1, B:C, 2, FALSE))

TRUE if VLOOKUP returns #N/A

=IF(ISNA(MATCH(A1, B:B, 0)), "Not Found", "Found")

Handle MATCH #N/A errors

=IFNA(VLOOKUP(A1, B:C, 2, FALSE), "Not Found")

Simpler #N/A handling

Note: Specifically tests for #N/A error. Use IFNA() for simpler handling

ISBLANK()

Information Functions

Tests if cell is empty

Syntax:

=ISBLANK(value)

Examples:

=ISBLANK(A1)

TRUE if A1 is empty

=IF(ISBLANK(A1), "Empty", A1)

Display 'Empty' if cell is blank

=COUNTIF(A1:A10, ISBLANK(A1:A10))

Count blank cells in range

Note: Returns TRUE only for truly empty cells

ISTEXT()

Information Functions

Tests if value is text

Syntax:

=ISTEXT(value)

Examples:

=ISTEXT(A1)

TRUE if A1 contains text

=IF(ISTEXT(A1), UPPER(A1), A1)

Convert to uppercase if text

=COUNTIF(A1:A10, ISTEXT(A1:A10))

Count text cells

Note: Returns FALSE for numbers, dates, and blank cells

AND()

Logical Functions

Returns TRUE if all conditions are true

Syntax:

=AND(logical1, [logical2], ...)

Examples:

=AND(A1>10, B1<100)

True if both conditions met

=AND(A1="Yes", B1="Yes")

True if both cells are 'Yes'

=IF(AND(A1>0, B1>0), "Both Positive", "Not Both")

Use with IF function

Note: All arguments must be TRUE for result to be TRUE

OR()

Logical Functions

Returns TRUE if any condition is true

Syntax:

=OR(logical1, [logical2], ...)

Examples:

=OR(A1>100, B1>100)

True if either condition met

=OR(A1="Red", A1="Blue")

True if cell is Red or Blue

=IF(OR(A1<0, B1<0), "Has Negative", "All Positive")

Use with IF function

Note: Only one argument needs to be TRUE for result to be TRUE

ISNUMBER()

Information Functions

Tests if value is a number

Syntax:

=ISNUMBER(value)

Examples:

=ISNUMBER(A1)

TRUE if A1 contains a number

=IF(ISNUMBER(A1), A1*2, "Not a number")

Multiply by 2 if number

=SUMPRODUCT(--(ISNUMBER(A1:A10)))

Count numeric cells

Note: Dates are considered numbers in Excel

NOT()

Logical Functions

Reverses the logic of its argument

Syntax:

=NOT(logical)

Examples:

=NOT(A1>100)

True if A1 is NOT greater than 100

=NOT(A1="")

True if A1 is NOT empty

=IF(NOT(A1=""), A1, "No Data")

Use with IF for non-empty check

Note: Changes TRUE to FALSE and FALSE to TRUE

SUBTOTAL()

Math & Statistical

Returns a subtotal in a list or database using specified function

Syntax:

=SUBTOTAL(function_num, ref1, [ref2], ...)

Examples:

=SUBTOTAL(9, A1:A10)

Sum of visible cells (function 9)

=SUBTOTAL(1, A1:A10)

Average of visible cells (function 1)

=SUBTOTAL(109, A1:A10)

Sum ignoring hidden rows and filtered data

Note: Functions 1-11 include hidden cells, 101-111 ignore hidden cells

ROUND()

Math & Statistical

Rounds a number to specified digits

Syntax:

=ROUND(number, num_digits)

Examples:

=ROUND(3.14159, 2)

Round to 2 decimal places (3.14)

=ROUND(1234.567, 0)

Round to nearest integer (1235)

=ROUND(1234.567, -2)

Round to nearest hundred (1200)

Note: Positive num_digits rounds to right of decimal, negative to left

TEXTJOIN()

Text Functions

Joins text with specified delimiter

Syntax:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Examples:

=TEXTJOIN(", ", TRUE, A1:A5)

Join range with commas, skip blanks

=TEXTJOIN(" | ", FALSE, A1, B1, C1)

Join with pipe separator

=TEXTJOIN(CHAR(10), TRUE, A1:A10)

Join with line breaks

Note: Available in Excel 2016+. ignore_empty: TRUE skips empty cells

WORKDAY()

Date & Time

Returns workday date that is specified days from start date

Syntax:

=WORKDAY(start_date, days, [holidays])

Examples:

=WORKDAY(TODAY(), 10)

Date 10 workdays from today

=WORKDAY(A1, 5, C1:C10)

5 workdays from A1, excluding holidays

=WORKDAY("1/1/2024", -5)

5 workdays before Jan 1

Note: Excludes weekends (Saturday/Sunday) and optional holidays

VLOOKUP()

Lookup & Reference

Looks up value in leftmost column and returns value from specified column

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Examples:

=VLOOKUP(A1, B:D, 2, FALSE)

Exact match lookup in column B, return from column C

=VLOOKUP("Apple", A1:C10, 3, TRUE)

Approximate match lookup

=VLOOKUP(A1, Sheet2!B:E, 4, FALSE)

Lookup from another sheet

Note: Use FALSE for exact match, TRUE for approximate match

HLOOKUP()

Lookup & Reference

Looks up value in top row and returns value from specified row

Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Examples:

=HLOOKUP(A1, B1:F5, 3, FALSE)

Exact match in row 1, return from row 3

=HLOOKUP("Q1", A1:E10, 2, FALSE)

Find Q1 and return value from row 2

=HLOOKUP(A1, Sheet2!A1:Z5, 4, TRUE)

Approximate match from another sheet

Note: Horizontal version of VLOOKUP

INDEX()

Lookup & Reference

Returns value at intersection of specified row and column

Syntax:

=INDEX(array, row_num, [column_num])

Examples:

=INDEX(A1:C10, 5, 2)

Value from row 5, column 2 of range

=INDEX(A:A, 10)

10th value in column A

=INDEX(A1:A10, MATCH("Apple", B1:B10, 0))

Use with MATCH for lookup

Note: Often combined with MATCH for flexible lookups

INDIRECT()

Lookup & Reference

Returns reference specified by text string

Syntax:

=INDIRECT(ref_text, [a1])

Examples:

=INDIRECT("A1")

Reference to cell A1

=INDIRECT("Sheet2!A1")

Reference to A1 on Sheet2

=INDIRECT(A1&"1")

Dynamic reference using cell value

Note: Reference updates when text changes. Can reference other sheets

TAKE()

Dynamic Array Functions

Takes specified number of rows/columns from array

Syntax:

=TAKE(array, rows, [columns])

Examples:

=TAKE(A1:C10, 5)

Take first 5 rows

=TAKE(A1:C10, -3)

Take last 3 rows

=TAKE(A1:C10, 5, 2)

Take first 5 rows and 2 columns

Note: Excel 365 Insider. Negative numbers take from end

DROP()

Dynamic Array Functions

Drops specified number of rows/columns from array

Syntax:

=DROP(array, rows, [columns])

Examples:

=DROP(A1:C10, 2)

Drop first 2 rows

=DROP(A1:C10, -1)

Drop last row

=DROP(A1:C10, 1, 1)

Drop first row and first column

Note: Excel 365 Insider. Negative numbers drop from end

XLOOKUP()

Lookup & Reference

Modern lookup function that replaces VLOOKUP and HLOOKUP

Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Examples:

=XLOOKUP(A1, B:B, C:C)

Basic lookup from B to C

=XLOOKUP(A1, B:B, C:C, "Not Found")

With custom error message

=XLOOKUP(A1, B:B, C:E)

Return multiple columns

Note: Available in Excel 365 and Excel 2021. More flexible than VLOOKUP

FILTER()

Dynamic Array Functions

Filters array based on criteria

Syntax:

=FILTER(array, include, [if_empty])

Examples:

=FILTER(A1:C10, B1:B10>100)

Filter rows where column B > 100

=FILTER(A1:A10, A1:A10<>"", "No Data")

Filter non-empty cells

=FILTER(A1:C10, (B1:B10="Apple")*(C1:C10>50))

Multiple criteria filter

Note: Excel 365 and Excel 2021. Returns dynamic array that spills

FORMULATEXT()

Lookup & Reference

Returns formula in a cell as text

Syntax:

=FORMULATEXT(reference)

Examples:

=FORMULATEXT(A1)

Shows formula in A1 as text

=FORMULATEXT(SUM(B1:B10))

Returns error (not a reference)

=IF(FORMULATEXT(A1)="", A1, FORMULATEXT(A1))

Show value or formula

Note: Returns #N/A if cell doesn't contain formula. Available in Excel 2013+

SINGLE()

Dynamic Array Functions

Returns single value from array with error checking

Syntax:

=SINGLE(array)

Examples:

=SINGLE(FILTER(A1:A10, A1:A10="Apple"))

Get single filtered result

=SINGLE(UNIQUE(A1:A10))

Error if more than one unique value

=SINGLE(B5)

Returns B5 value (identity function)

Note: Excel 365 Insider. Returns error if array has more than one value

SWITCH()

Advanced Functions

Evaluates expression against list of values and returns result

Syntax:

=SWITCH(expression, value1, result1, [value2, result2], ..., [default])

Examples:

=SWITCH(A1, 1, "One", 2, "Two", 3, "Three", "Other")

Convert numbers to words

=SWITCH(WEEKDAY(TODAY()), 1, "Sunday", 7, "Saturday", "Weekday")

Check if weekend

=SWITCH(LEFT(A1,1), "A", "Apple", "B", "Banana", "Unknown")

Based on first letter

Note: Available in Excel 2016+. Cleaner alternative to nested IF statements

LEFT()

Text Functions

Extracts characters from left side of text

Syntax:

=LEFT(text, [num_chars])

Examples:

=LEFT(A1, 3)

Extract first 3 characters

=LEFT("Hello World", 5)

Returns 'Hello'

=LEFT(A1)

Extract first character (default)

Note: Default num_chars is 1 if omitted

RIGHT()

Text Functions

Extracts characters from right side of text

Syntax:

=RIGHT(text, [num_chars])

Examples:

=RIGHT(A1, 3)

Extract last 3 characters

=RIGHT("Hello World", 5)

Returns 'World'

=RIGHT(A1)

Extract last character (default)

Note: Default num_chars is 1 if omitted

CHOOSE()

Lookup & Reference

Chooses value from list based on index number

Syntax:

=CHOOSE(index_num, value1, [value2], ...)

Examples:

=CHOOSE(A1, "Red", "Blue", "Green")

Return color based on number 1-3

=CHOOSE(WEEKDAY(TODAY()), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")

Day name from today's date

=CHOOSE(A1, B:B, C:C, D:D)

Choose column based on index

Note: Index starts at 1. Returns error if index is out of range

MODE()

Statistical Functions

Returns the most frequently occurring value

Syntax:

=MODE(number1, [number2], ...)

Examples:

=MODE(A1:A10)

Most common value in range

=MODE(1, 2, 2, 3, 4)

Returns 2 (appears twice)

=MODE.SNGL(A1:A10)

Single mode (newer Excel versions)

Note: Returns error if no value appears more than once

MODE.SNGL()

Statistical Functions

Returns the most frequently occurring single value

Syntax:

=MODE.SNGL(number1, [number2], ...)

Examples:

=MODE.SNGL(A1:A10)

Most common single value in range

=MODE.SNGL(1, 2, 2, 3, 4)

Returns 2 (appears twice)

=MODE.SNGL(1, 1, 2, 2, 3)

Returns first mode found (1)

Note: Replaces MODE function in Excel 2010+. Returns error if no duplicate values

MODE.MULT()

Statistical Functions

Returns vertical array of most frequently occurring values

Syntax:

=MODE.MULT(array)

Examples:

=MODE.MULT(A1:A10)

All most frequently occurring values

=MODE.MULT({1,1,2,2,3,4})

Returns array with 1 and 2

=INDEX(MODE.MULT(A1:A10), 1)

Get first mode from array

Note: Returns array formula. Available in Excel 2010+. Use Ctrl+Shift+Enter in older versions

SUM()

Math & Statistical

Adds all numbers in a range

Syntax:

=SUM(number1, [number2], ...)

Examples:

=SUM(A1:A10)

Sum all values in range A1 to A10

=SUM(A1, B1, C1)

Sum specific cells

=SUM(A1:A5, C1:C5)

Sum multiple ranges

Note: Ignores text values and empty cells

AVERAGE()

Math & Statistical

Calculates the average of numbers

Syntax:

=AVERAGE(number1, [number2], ...)

Examples:

=AVERAGE(A1:A10)

Average of range A1 to A10

=AVERAGE(A1, B1, C1)

Average of specific cells

=AVERAGE(A:A)

Average of entire column A

Note: Excludes empty cells and text from calculation

COUNT()

Math & Statistical

Counts cells containing numbers

Syntax:

=COUNT(value1, [value2], ...)

Examples:

=COUNT(A1:A10)

Count numeric cells in range

=COUNT(A:A)

Count numeric cells in column A

=COUNT(A1, B1, C1)

Count specific numeric cells

Note: Only counts cells with numeric values

MAX()

Math & Statistical

Returns the largest value

Syntax:

=MAX(number1, [number2], ...)

Examples:

=MAX(A1:A10)

Find maximum value in range

=MAX(A1, B1, C1)

Find maximum among specific cells

=MAX(A:A)

Find maximum in entire column

Note: Ignores text and logical values

MIN()

Math & Statistical

Returns the smallest value

Syntax:

=MIN(number1, [number2], ...)

Examples:

=MIN(A1:A10)

Find minimum value in range

=MIN(A1, B1, C1)

Find minimum among specific cells

=MIN(A:A)

Find minimum in entire column

Note: Ignores text and logical values

Master Excel formulas with practice and repetition. Bookmark this page for quick reference!

📊 102 Total Formulas 🏷️ 10 Categories ⚡ Quick Search