#Count
Formula Related Functions
Count between dates by age range COUNTIFS FIND LEFT RIGHT SUMPRODUCT TEXTBEFORE
Count birthdays by year SUMPRODUCT YEAR COUNTIFS BYROW UNIQUE SORT LET
Count cells between dates COUNTIFS SUMPRODUCT
Count cells between two numbers COUNTIFS SUMPRODUCT
Count cells equal to COUNTIF SUMPRODUCT
Count cells equal to case sensitive SUMPRODUCT EXACT
Count cells equal to one of many things COUNTIF SUMPRODUCT ISNUMBER MATCH
Count cells equal to this or that COUNTIF SUM SUMPRODUCT
Count cells greater than COUNTIF COUNTIFS
Count cells less than COUNTIF COUNTIFS
Count cells not between two numbers COUNTIF SUMPRODUCT
Count cells not equal to COUNTIF COUNTIFS
Count cells not equal to many things MATCH ISNA SUMPRODUCT COUNTA COUNTIF
Count cells not equal to x or y COUNTIFS SUMPRODUCT
Count cells over n characters SUMPRODUCT LEN N
Count cells that are blank COUNTBLANK COUNTA COUNTIF SUMPRODUCT
Count cells that are not blank COUNTA COUNTBLANK COUNTIFS SUMPRODUCT
Count cells that begin with COUNTIF SUMPRODUCT LEFT LEN EXACT
Count cells that contain case sensitive SUMPRODUCT ISNUMBER FIND
Count cells that contain either x or y SUMPRODUCT ISNUMBER FIND COUNTIF
Count cells that contain errors SUMPRODUCT ISERROR ISERR ERROR.TYPE IFERROR
Count cells that contain n characters COUNTIF SUMPRODUCT LEN REPT
Count cells that contain negative numbers COUNTIF SUMPRODUCT
Count cells that contain numbers COUNT SUMPRODUCT ISNUMBER
Count cells that contain odd numbers SUMPRODUCT ISODD MOD ISEVEN
Count cells that contain positive numbers COUNTIF SUMPRODUCT
Count cells that contain specific text COUNTIF SUMPRODUCT SEARCH FIND ISNUMBER
Count cells that contain text COUNTIF COUNTIFS SUMPRODUCT ISTEXT NOT
Count cells that do not contain COUNTIF SUMPRODUCT SEARCH FIND NOT
Count cells that do not contain errors ISERROR NOT SUMPRODUCT ISERR COUNTIF
Count cells that do not contain many strings ISNUMBER SEARCH MMULT TRANSPOSE REDUCE
Count cells that end with COUNTIF
Count columns that contain specific values MMULT COLUMN TRANSPOSE BYCOL LAMBDA
Count dates by day of week SUMPRODUCT WEEKDAY MATCH
Count dates in given year YEAR SUMPRODUCT
Count if row meets internal criteria SUMPRODUCT
Count if row meets multiple criteria SUMPRODUCT
Count if two criteria match COUNTIFS SUMPRODUCT
Count items in list COUNTIFS
Count long numbers SUMPRODUCT COUNTIF
Count matches between two columns SUMPRODUCT
Count matching values in matching columns SUMPRODUCT ISNUMBER MATCH SEARCH
Count missing values COUNTIF SUM SUMPRODUCT MATCH ISNA
Count non-blank cells by category COUNTIFS
Count not equal to multiple criteria SUMPRODUCT ISNA MATCH COUNTIFS
Count numbers by nth digit SUMPRODUCT MID
Count numbers by range COUNTIFS FREQUENCY DROP
Count numbers that begin with SUMPRODUCT LEFT LEN COUNTIF
Count numbers with leading zeros SUMPRODUCT COUNTIF SUMIF
Count occurrences in entire workbook COUNTIF SUMPRODUCT VSTACK
Count or sum variance SUMPRODUCT SUM ABS
Count or sum whole numbers only MOD SUMPRODUCT
Count paired items in listed combinations COUNTIFS CONCAT
Count rows that contain specific values MMULT COLUMN TRANSPOSE BYROW LAMBDA
Count rows with at least n matching values MMULT COLUMN TRANSPOSE
Count rows with multiple OR criteria SUMPRODUCT
Count rows with OR logic SUMPRODUCT
Count sold and remaining COUNTA COUNTIF
Count total matches in two ranges SUMPRODUCT COUNTIF MATCH ISNUMBER COUNT
Count unique dates UNIQUE COUNT COUNTIF SUMPRODUCT
Count unique numeric values in a range FREQUENCY SUM COUNTIF
Count unique numeric values with criteria FREQUENCY SUM
Count unique text values in a range FREQUENCY MATCH ROW SUMPRODUCT
Count unique text values with criteria FREQUENCY MATCH ROW SUM
Count unique values in a range with COUNTIF SUMPRODUCT COUNTIF
Count visible rows in a filtered list SUBTOTAL
Count visible rows with criteria SUBTOTAL OFFSET SUMPRODUCT INDEX
COUNTIF with non-contiguous range COUNTIF INDIRECT VSTACK
COUNTIFS with multiple criteria and OR logic COUNTIFS
Histogram with FREQUENCY FREQUENCY
Running count of occurrence in list COUNTIF IF
Summary count by month with COUNTIFS COUNTIFS EDATE
Summary count with COUNTIF COUNTIF
Summary count with percentage breakdown COUNTIF COUNTA
SUMPRODUCT count multiple OR criteria SUMPRODUCT
Two-way summary count COUNTIFS LET UNIQUE HSTACK VSTACK
#Sum
Formula Related Functions
Calculate running total SUM SCAN
Count cells that contain formulas SUMPRODUCT ISFORMULA NOT
Subtotal by color SUMIF COUNTIF
Subtotal by invoice number SUMIF COUNTIF
Subtotal invoices by age SUMIF COUNTIFS
Sum across multiple worksheets SUM
Sum across multiple worksheets with criteria SUMPRODUCT SUMIF INDIRECT
Sum and ignore errors SUM SUMIF AGGREGATE IFERROR
Sum bottom n values SUMPRODUCT SMALL SUM ROW INDIRECT
Sum bottom n values with criteria SMALL SUM FILTER SEQUENCE
Sum by group SUMIF SUMIFS IF
Sum by month SUMIFS EDATE SUMPRODUCT TEXT
Sum by month ignore year SUMPRODUCT MONTH
Sum by month in columns SUMIFS EOMONTH
Sum by quarter SUMIFS ROUNDUP MONTH SUMPRODUCT LET LAMBDA BYROW
Sum by week SUMIFS LET LAMBDA UNIQUE WEEKDAY BYROW HSTACK VSTACK
Sum by week number SUMIFS WEEKNUM LAMBDA LET BYROW UNIQUE VSTACK HSTACK
Sum by weekday SUMPRODUCT TEXT WEEKDAY
Sum by year SUMPRODUCT YEAR SUMIFS DATE LET BYROW UNIQUE VSTACK HSTACK
Sum columns based on adjacent criteria SUMPRODUCT
Sum entire column SUM
Sum entire row SUM
Sum every n rows SUM OFFSET
Sum every nth column FILTER SEQUENCE SUMPRODUCT COLUMN MOD
Sum every nth row FILTER SEQUENCE MOD SUM SUMPRODUCT ROW
Sum first n matching values FILTER TAKE SUM
Sum first n rows TAKE SUM OFFSET
Sum formulas only SUMPRODUCT ISFORMULA NOT
Sum if begins with SUMIF SUMIFS
Sum if between SUMIFS
Sum if case-sensitive SUMPRODUCT EXACT
Sum if cell contains text in another cell SUMIF SUMIFS SUMPRODUCT FIND ISNUMBER
Sum if cells are equal to SUMIF SUMIFS
Sum if cells are not equal to SUMIF SUMIFS
Sum if cells contain an asterisk SUMIF SUMIFS FIND FILTER
Sum if cells contain both x and y SUMIFS
Sum if cells contain either x or y SUMIFS SUMPRODUCT ISNUMBER FIND SEARCH
Sum if cells contain specific text SUMIF SUMIFS SUMPRODUCT FIND ISNUMBER
Sum if date is between SUMIFS DATE
Sum if date is greater than SUMIFS SUMIF DATE
Sum if ends with SUMIF SUMIFS
Sum if greater than SUMIF SUMIFS
Sum if less than SUMIF SUMIFS
Sum if multiple columns SUMPRODUCT SUMIFS
Sum if multiple criteria SUMIFS
Sum if not blank SUMIFS SUMPRODUCT FILTER SUM
Sum if one of many things SUMIFS SUMPRODUCT ISNUMBER MATCH FILTER
Sum if x or y SUMIF SUMPRODUCT ISNUMBER MATCH
Sum last 30 days SUMIFS TODAY SUMPRODUCT FILTER
Sum last n columns TAKE OFFSET COLUMNS INDEX SUM
Sum last n rows TAKE SUM OFFSET INDEX
Sum matching columns SUMPRODUCT LEFT FILTER SUM
Sum matching columns and rows SUMPRODUCT FILTER SUM
Sum numbers in single cell TEXTSPLIT SUM FILTERXML SUBSTITUTE
Sum top n values SUM LARGE SEQUENCE SUMPRODUCT ROW INDIRECT
Sum top n values with criteria SUM LARGE FILTER SEQUENCE IF
Sum visible rows in a filtered list SUBTOTAL AGGREGATE
SUMIFS with horizontal range SUMIFS
SUMIFS with multiple criteria and OR logic SUMIFS SUM
SUMPRODUCT with IF SUMPRODUCT IF
#Average
Formula Related Functions
Average and ignore errors AVERAGEIF AGGREGATE AVERAGE IFERROR FILTER
Average by group UNIQUE AVERAGEIFS
Average by month AVERAGEIFS EDATE FILTER AVERAGE
Average call time per month AVERAGEIFS EDATE FILTER AVERAGE TEXT
Average hourly pay per day SUMPRODUCT SUMIFS
Average if not blank AVERAGEIFS FILTER AVERAGE
Average if with filter AVERAGE FILTER SUMPRODUCT IF AVERAGEIFS
Average last 3 numeric values AVERAGE FILTER TAKE LOOKUP LARGE ROW
Average last n columns TAKE AVERAGE OFFSET COUNT INDEX COLUMNS
Average last n rows TAKE AVERAGE OFFSET COUNT
Average numbers ignore zero AVERAGEIF AVERAGEIFS AVERAGE FILTER
Average salary by department UNIQUE AVERAGEIFS
Average top 3 scores LARGE AVERAGE SEQUENCE
Average with multiple criteria AVERAGEIFS
Basic average example AVERAGE AVERAGEIFS AGGREGATE
Moving average formula OFFSET AVERAGE MIN
Must pass 4 out of 6 subjects IF COUNTIF AND
Weighted average SUMPRODUCT SUM AVERAGE TRANSPOSE
#Dynamic array
Formula Related Functions
Basic filter example FILTER
Biggest gainers and losers FILTER LARGE SMALL SORT
Combine data in multiple worksheets VSTACK FILTER LET
Combine ranges INDEX SEQUENCE ROWS COLUMNS LET
Count unique dates ignore time INT UNIQUE COUNT LET LAMBDA SCAN
Count unique values UNIQUE COUNTA
Count unique values with criteria UNIQUE FILTER LEN SUM COUNTA
Detailed LET function example LET VLOOKUP IF XLOOKUP
Distinct values UNIQUE
Dynamic summary count UNIQUE COUNTIF LET SORT SCAN LAMBDA
Dynamic two-way average UNIQUE TRANSPOSE AVERAGEIFS
Dynamic two-way count UNIQUE TRANSPOSE COUNTIFS LET HSTACK VSTACK
Dynamic two-way sum UNIQUE TRANSPOSE AVERAGEIFS
Extract common values from two lists FILTER COUNTIF UNIQUE SORT
Filter and sort without errors FILTER SORT
Filter and transpose horizontal to vertical FILTER TRANSPOSE
Filter by column, sort by row FILTER SORT
Filter by date FILTER MONTH
FILTER case-sensitive FILTER SEARCH ISNUMBER
Filter contains one of many FILTER ISNUMBER MATCH
Filter data between dates FILTER DATE
Filter every nth row FILTER SEQUENCE MOD ROW
Filter exclude blank values FILTER
Filter horizontal data FILTER TRANSPOSE
FILTER last n valid entries FILTER SEQUENCE INDEX
FILTER on first or last n values FILTER SEQUENCE INDEX
FILTER on top n values FILTER LARGE SORT
FILTER on top n values with criteria FILTER LARGE SORT
Filter text contains FILTER SEARCH ISNUMBER
Filter this or that FILTER
Filter to extract matching values FILTER COUNTIF COUNTIFS
FILTER to remove columns FILTER MATCH ISNUMBER
FILTER to show duplicate values UNIQUE FILTER COUNTIF
Filter values within tolerance FILTER ABS
FILTER with complex multiple criteria FILTER LEFT MONTH NOT
Filter with multiple criteria FILTER
FILTER with multiple OR criteria FILTER ISNUMBER MATCH
FILTER with partial match FILTER SEARCH ISNUMBER
Generate random text strings SORTBY RANDARRAY COUNTA SEQUENCE CHAR
Get column totals BYCOL LAMBDA MMULT ROW TRANSPOSE SEQUENCE
Get row totals BYROW LAMBDA MMULT COLUMN TRANSPOSE SEQUENCE
LAMBDA append range LAMBDA LET INDEX SEQUENCE
LAMBDA append range horizontal LAMBDA LET INDEX SEQUENCE
LAMBDA contains one of many LAMBDA SUMPRODUCT SUM ISNUMBER SEARCH
LAMBDA contains which things LAMBDA LET ISNUMBER SEARCH SORTBY
LAMBDA count words LAMBDA SUBSTITUTE TRIM LEN
LAMBDA replace characters recursive LAMBDA LEFT MID LEN
LAMBDA split text to array LAMBDA FILTERXML TRANSPOSE
LAMBDA strip characters LAMBDA LET MID MATCH ISNUMBER
LAMBDA strip trailing characters recursive LAMBDA MID LEN
List upcoming birthdays SORTBY LET TEXT INDEX SEQUENCE XMATCH
MAP with AND and OR logic MAP AND OR
Minimum value if unique UNIQUE MIN COUNTIF IF
Random list of names SORTBY RANDARRAY COUNTA SEQUENCE
Random numbers without duplicates SEQUENCE SORTBY RANDARRAY INDEX
Random sort SORTBY RANDARRAY ROWS
Remove blank rows FILTER BYROW
Sort birthdays by month and day SORTBY TEXT
Sort by custom list SORTBY MATCH
Sort by one column SORT
Sort by substring SORTBY TEXTBEFORE TEXTAFTER
Sort by two columns SORTBY
Sort text by length SORTBY LEN
Sort values by columns SORT SORTBY
Sum numbers with text TEXTBEFORE TEXTAFTER UNIQUE LET LAMBDA BYROW
Unique rows UNIQUE SORT
Unique values UNIQUE
Unique values by count UNIQUE FILTER COUNTIF
Unique values case-sensitive REDUCE LAMBDA EXACT VSTACK
Unique values from multiple ranges UNIQUE VSTACK
Unique values ignore blanks UNIQUE FILTER
Unique values with criteria UNIQUE FILTER
Unique values with multiple criteria UNIQUE FILTER
UNIQUE with non-adjacent columns FILTER UNIQUE SORT
XLOOKUP match any column MMULT SEQUENCE COLUMNS
#Min and Max
Formula Related Functions
Cap percentage between 0 and 100 MIN MAX MEDIAN IF
Find lowest n values SMALL INDEX MATCH
First in last out times MINIFS MAXIFS MIN MAX IF FILTER
Large with criteria LARGE FILTER IF
Larger of two values MAX IF
Max by month MAXIFS MAX TEXT
Max of every nth column MAX FILTER SEQUENCE COLUMNS CHOOSECOLS COLUMN MOD
Max value ignore all errors AGGREGATE MAXIFS
Max value on given weekday FILTER TEXT MAX IF AGGREGATE
Max value with variable column INDEX MATCH MAX FILTER COUNTIF
Maximum change MAX INDEX MATCH XLOOKUP LET HSTACK SORT VSTACK
Maximum if multiple criteria MAX IF MAXIFS FILTER
Maximum value MAX MAXIFS LARGE
Maximum value if MAXIFS MAX IF FILTER BYROW VSTACK HSTACK UNIQUE
Minimum if multiple criteria MIN IF MINIFS
Minimum value MIN MINIFS SMALL
Minimum value if MINIFS MIN FILTER IF BYROW VSTACK HSTACK UNIQUE
Name of nth largest value LARGE INDEX MATCH XLOOKUP
Name of nth largest value with criteria LARGE INDEX MATCH IF XLOOKUP
nth largest value LARGE
nth largest value with criteria LARGE FILTER IF
nth largest without duplicates MAX UNIQUE IF
nth smallest value SMALL
nth smallest value with criteria SMALL
Smaller of two values MIN IF
#Lookup
Formula Related Functions
Case sensitive lookup EXACT INDEX MATCH XLOOKUP
Find closest match XLOOKUP INDEX MATCH ABS MIN
Find longest string XLOOKUP FILTER INDEX MATCH MAX LEN
Find longest string with criteria XLOOKUP INDEX MATCH MAX LEN
Find missing values COUNTIF IF MATCH ISNUMBER
Get address of lookup result CELL XLOOKUP INDEX MATCH
Get all matches cell contains SEARCH ISNUMBER FILTER TEXTJOIN
Get cell content at given row and column ADDRESS INDIRECT INDEX
Get employee information with VLOOKUP VLOOKUP XLOOKUP CHOOSECOLS TAKE DROP
Get first match cell contains XLOOKUP ISNUMBER SEARCH INDEX MATCH AGGREGATE
Get first non-blank value in a list XLOOKUP NOT ISBLANK INDEX MATCH
Get first numeric value in a range XLOOKUP ISNUMBER INDEX MATCH
Get first text value in a range XLOOKUP VLOOKUP INDEX MATCH ISTEXT
Get first text value in a row HLOOKUP XLOOKUP ISTEXT
Get information about max value TAKE SORT MAX MATCH INDEX
Get last match XLOOKUP XMATCH FILTER TAKE LOOKUP INDEX
Get last match cell contains XLOOKUP LOOKUP SEARCH INDEX MATCH ISNUMBER AGGREGATE
Get location of value in 2D array SUMPRODUCT ROW COLUMN
Get nth match FILTER INDEX CHOOSEROWS SMALL IF MIN ROW
Get nth match with INDEX / MATCH INDEX SMALL IF
Get nth match with VLOOKUP VLOOKUP COUNTIF
Get value of last non-empty cell LOOKUP XLOOKUP
INDEX and MATCH advanced example INDEX MATCH LEFT
INDEX and MATCH all matches INDEX MATCH AND SUM
INDEX and MATCH all partial matches INDEX AGGREGATE ISNUMBER SEARCH
INDEX and MATCH approximate match INDEX MATCH
INDEX and MATCH approximate match with multiple criteria INDEX MATCH IF
INDEX and MATCH case-sensitive INDEX MATCH EXACT XLOOKUP
INDEX and MATCH descending order INDEX MATCH
INDEX and MATCH exact match INDEX MATCH TRANSPOSE
Index and match on multiple columns MMULT COLUMN TRANSPOSE INDEX
INDEX and MATCH two-column lookup INDEX XMATCH XLOOKUP CHOOSECOLS
INDEX and MATCH with multiple criteria INDEX MATCH
INDEX and MATCH with variable columns INDEX XMATCH XLOOKUP CHOOSECOLS
INDEX with variable array INDEX MATCH CHOOSE
Join tables with INDEX and MATCH INDEX MATCH
Left lookup with INDEX and MATCH INDEX MATCH
Left lookup with VLOOKUP VLOOKUP CHOOSE
List missing values FILTER COUNTIF NOT
Look up entire column XLOOKUP INDEX MATCH
Look up entire row XLOOKUP INDEX MATCH
Lookup and sum column INDEX MATCH SUM
Lookup first negative value XLOOKUP FILTER TAKE INDEX MATCH
Lookup last file version LOOKUP ISNUMBER FIND
Lookup latest price LOOKUP
Lookup lowest Monday tide INDEX MATCH IF MIN XLOOKUP
Lookup lowest value INDEX MATCH MIN
Lookup number plus or minus N XLOOKUP ABS INDEX MATCH
Lookup up cost for product or service VLOOKUP
Lookup value between two numbers LOOKUP
Lookup with variable sheet name VLOOKUP INDIRECT
Match first does not begin with INDEX MATCH LEFT
Match first error MATCH ISERROR
Match first occurrence does not contain INDEX MATCH ISNUMBER SEARCH
Match long text MATCH LEFT MID EXACT
Match next highest value INDEX MATCH
Max if criteria match MAX IF MAXIFS
Merge tables with VLOOKUP VLOOKUP
Multi-criteria lookup and transpose INDEX MATCH
Multiple chained VLOOKUPs VLOOKUP IFERROR
Multiple matches in comma separated list TEXTJOIN
Multiple matches into separate columns FILTER UNIQUE SORT SEQUENCE INDEX SMALL IFERROR COLUMN COLUMNS
Multiple matches into separate rows FILTER UNIQUE SORT TRANSPOSE INDEX SMALL IFERROR ROW ROWS
Nearest location with XMATCH INDEX XMATCH
Next largest match with the MATCH function INDEX MATCH
Partial match with numbers and wildcard MATCH TEXT
Partial match with VLOOKUP VLOOKUP
Position of first partial match MATCH INDEX
Position of max value in list MAX MATCH
Quantity based discount XLOOKUP INDEX MATCH IFNA
Rank and score with INDEX and MATCH RANK INDEX MATCH
Reverse VLOOKUP example VLOOKUP CHOOSE INDEX MATCH XLOOKUP
Self-contained VLOOKUP VLOOKUP
Sum lookup values using SUMIF SUMIF SUMPRODUCT
Sum range with INDEX INDEX SUM
SUMIFS multiple criteria lookup in table SUMIFS
SUMPRODUCT case-sensitive lookup SUMPRODUCT EXACT
Two-way approximate match multiple criteria INDEX MATCH IF
Two-way lookup with INDEX and MATCH INDEX MATCH
VLOOKUP by date VLOOKUP
VLOOKUP calculate grades VLOOKUP
VLOOKUP calculate shipping cost VLOOKUP MAX
VLOOKUP case-sensitive VLOOKUP EXACT CHOOSE
VLOOKUP faster VLOOKUP VLOOKUP NA
VLOOKUP from another sheet VLOOKUP
VLOOKUP from another workbook VLOOKUP
VLOOKUP if blank return blank VLOOKUP IF
VLOOKUP override output VLOOKUP
VLOOKUP tax rate calculation VLOOKUP
VLOOKUP two-way lookup VLOOKUP MATCH
VLOOKUP variable commission split VLOOKUP
VLOOKUP with 2 lookup tables VLOOKUP
VLOOKUP with multiple criteria VLOOKUP
VLOOKUP with multiple criteria advanced VLOOKUP CHOOSE
VLOOKUP with numbers and text VLOOKUP ISTEXT IFERROR
VLOOKUP with two client rates VLOOKUP
VLOOKUP with variable table array INDIRECT VLOOKUP IF
VLOOKUP without #N/A error VLOOKUP IFERROR IFNA ISNA
XLOOKUP approximate match with multiple criteria XLOOKUP IF FILTER
XLOOKUP basic approximate match XLOOKUP
XLOOKUP basic exact match XLOOKUP
XLOOKUP binary search XLOOKUP INDEX XMATCH
XLOOKUP case-sensitive XLOOKUP EXACT
XLOOKUP date of max value XLOOKUP INDEX MATCH
XLOOKUP horizontal lookup XLOOKUP
XLOOKUP last match XLOOKUP
XLOOKUP latest by date XLOOKUP MAX
XLOOKUP lookup left XLOOKUP
XLOOKUP lookup row or column XLOOKUP
XLOOKUP match text contains XLOOKUP SEARCH FIND ISNUMBER
XLOOKUP rearrange columns XLOOKUP
XLOOKUP return blank if blank XLOOKUP IF LET
XLOOKUP two-way exact match XLOOKUP INDEX MATCH
XLOOKUP wildcard contains substring XLOOKUP VLOOKUP TRANSPOSE
XLOOKUP wildcard match example XLOOKUP TRANSPOSE
XLOOKUP with Boolean OR logic XLOOKUP INDEX MATCH
XLOOKUP with complex multiple criteria FILTER LEFT MONTH NOT
XLOOKUP with logical criteria XLOOKUP
XLOOKUP with multiple criteria XLOOKUP INDEX MATCH
XLOOKUP without #N/A error XLOOKUP IFERROR IFNA
XMATCH reverse search XMATCH INDEX
XMATCH with multiple criteria XMATCH XLOOKUP INDEX MATCH
Zodiac sign lookup INDEX MATCH TEXT DATEVALUE IFNA YEAR
#If
Formula Related Functions
Calculate sales commission with if IF IFS
If cell begins with x, y, or z OR LEFT IF COUNTIF
If cell contains IF SEARCH ISNUMBER
if cell contains this or that SEARCH ISNUMBER OR COUNTIF SUM
If cell equals IF
If cell is blank IF ISBLANK
If cell is greater than IF
If cell is not blank IF ISBLANK NOT
If cell is this OR that IF OR
If cell is x or y and z IF OR AND
If complete show checkmark IF UNICHAR CHAR
If date is between two dates IF AND
If else IF IFS VLOOKUP
If not blank multiple cells IF ISBLANK NOT XLOOKUP
If NOT this or that IF OR NOT
If this AND that IF AND
If this AND that OR that IF AND OR
IF with boolean logic IF
IF with other calculations IF AND
IF with wildcards IF COUNTIF SEARCH ISNUMBER
Invoice age and status IF TODAY
Nested IF function example IF IFS
Nested IF with multiple AND IF AND OR
Only calculate if not blank IF COUNT ISBLANK COUNTBLANK AND OR
Return blank if IF ISBLANK COUNTBLANK
#Grouping
Formula Related Functions
Categorize text with keywords INDEX MATCH ISNUMBER SEARCH XLOOKUP
Group arbitrary text values VLOOKUP
Group numbers at uneven intervals LOOKUP
Group numbers with VLOOKUP VLOOKUP
Group times into 3 hour buckets FLOOR
Group times into unequal buckets VLOOKUP
If cell contains one of many things INDEX MATCH ISNUMBER SEARCH
Map inputs to arbitrary values VLOOKUP CHOOSE
Map text to numbers VLOOKUP
Running count group by n size COUNTA CEILING
#Conditional formatting
Formula Related Functions
Highlight 3 smallest values with criteria SMALL AND
Conditional formatting based on another cell
Conditional formatting based on another column
Conditional formatting column is blank OR AND NOT
Conditional formatting date past due TODAY
Conditional formatting dates overlap SUMPRODUCT
Conditional formatting highlight target percentage
Conditional formatting last n rows
Find duplicate values in two columns COUNTIF AND
Gantt chart AND
Gantt chart by week AND
Gantt chart time schedule AND OR
Gantt chart with weekends WEEKDAY
Highlight approximate match lookup conditional formatting LOOKUP OR AND
Highlight blank cells ISBLANK LEN
Highlight bottom values SMALL
Highlight cells that begin with COUNTIF FIND IFERROR
Highlight cells that contain SEARCH FIND ISNUMBER
Highlight cells that contain one of many SEARCH FIND ISNUMBER SUMPRODUCT
Highlight cells that end with COUNTIF RIGHT LEN EXACT
Highlight cells that equal EXACT
Highlight column differences EXACT NOT
Highlight data by quartile QUARTILE
Highlight dates between DATE AND
Highlight dates greater than DATE
Highlight dates in same month and year DATE TEXT
Highlight dates in the next N days TODAY AND
Highlight dates that are weekends WEEKDAY OR
Highlight duplicate columns SUMPRODUCT COUNTIF
Highlight duplicate rows COUNTIFS COUNTIF SUMPRODUCT
Highlight duplicate values COUNTIF
Highlight entire rows
Highlight every other row ISEVEN ISODD ROW MOD
Highlight integers only MOD
Highlight many matching values COUNTIF
Highlight missing values COUNTIF
Highlight multiples of specific value MOD
Highlight numbers that include symbols ISNUMBER MID
Highlight row and column intersection exact match OR AND
Highlight rows that contain SEARCH FIND
Highlight rows with blank cells COUNTBLANK
Highlight rows with dates between DATE AND
Highlight specific day of week TEXT
Highlight top values LARGE
Highlight unique values COUNTIF
Highlight unprotected cells CELL
Highlight values between AND
Highlight values greater than
Highlight values not between X and Y AND NOT
Shade alternating groups of n rows ROW CEILING ISEVEN ISODD
#Data validation
Formula Related Functions
Data validation allow numbers only ISNUMBER
Data validation allow text only ISTEXT
Data validation allow uppercase only UPPER EXACT AND
Data validation allow weekday only YEAR TODAY
Data validation date in next 30 days AND TODAY
Data validation date in specific year YEAR TODAY
Data validation don't exceed total SUM
Data validation exists in list COUNTIF
Data validation must begin with EXACT LEFT COUNTIF
Data validation must contain specific text FIND ISNUMBER
Data validation must not contain ISNUMBER SEARCH SUMPRODUCT
Data validation must not exist in list COUNTIF
Data validation no punctuation FIND COUNT
Data validation only dates between AND DATE
Data validation require specific multiple MOD
Data validation require unique number AND ISNUMBER COUNTIF
Data validation specific characters only MATCH COUNT LEN MID INDIRECT
Data validation unique values only COUNTIF
Data validation whole percentage only TRUNC AND
Data validation with conditional list IF
#Rank
Formula Related Functions
Break ties with helper column and COUNTIF SMALL INDEX MATCH
Rank function example RANK
Rank if formula COUNTIFS RANK
Rank race results RANK
Rank values by month LARGE INDEX MATCH TEXT IF
Rank with ordinal suffix CHOOSE ABS MOD
Rank without ties RANK COUNTIF
#Round
Formula Related Functions
Get decimal part of a number TRUNC
Get integer part of a number TRUNC INT
Get number at place value MOD
Round a number ROUND
Round a number down ROUNDDOWN
Round a number down to nearest multiple FLOOR
Round a number to n significant digits ROUND LOG10 INT ABS
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 MROUND
Round by bundle size CEILING
Round price to end in .45 or .95 CEILING
Round time to nearest 15 minutes MROUND CEILING FLOOR
Round to nearest 1000 ROUND
Round to nearest 5 MROUND CEILING FLOOR
#Date and Time
Formula Related Functions
Add business days to date WORKDAY NETWORKDAYS
Add days exclude certain days of week WORKDAY.INTL
Add days to date DAY
Add decimal hours to time TIME MOD
Add decimal minutes to time TIME MOD
Add months to date EDATE
Add workdays no weekends WORKDAY.INTL NETWORKDAYS
Add workdays to date custom weekends WORKDAY.INTL NETWORKDAYS
Add years to date DATE YEAR MONTH DAY EDATE
Assign points based on late time IF VALUE
Basic timesheet formula with breaks MOD
Calculate date overlap in days MAX MIN
Calculate days open TODAY IF ISBLANK
Calculate days remaining TODAY
Calculate expiration date EOMONTH EDATE
Calculate number of hours between two times IF MOD
Calculate retirement date EDATE YEARFRAC SIGN
Calculate years between dates YEARFRAC INT
Convert date string to date time DATEVALUE LEFT MID TIMEVALUE
Convert date to Julian format DATE YEAR TEXT
Convert date to month and year TEXT
Convert date to text TEXT
Convert decimal hours to Excel time
Convert decimal minutes to Excel time
Convert decimal seconds to Excel time
Convert Excel time to decimal hours
Convert Excel time to decimal minutes
Convert Excel time to decimal seconds
Convert Excel time to Unix time DATE
Convert text date dd/mm/yy to mm/dd/yy DATE LEFT MID RIGHT TRIM
Convert text timestamp into time TIME MID
Convert text to date DATE LEFT MID RIGHT
Convert time to money
Convert time to time zone MOD
Convert Unix time stamp to Excel date DATE
Count birthdays by month SUMPRODUCT MONTH
Count calls at specific times COUNTIFS
Count dates in current month COUNTIFS EOMONTH SUMPRODUCT N
Count day of week between dates WEEKDAY ROW INDIRECT SUMPRODUCT SEQUENCE
Count holidays between two dates SUMPRODUCT
Count times in a specific range COUNTIFS TIME
Create date range from two dates TEXT IF
Custom weekday abbreviation WEEKDAY CHOOSE
Date is same month MONTH
Date is same month and year MONTH YEAR
Date is workday WORKDAY WORKDAY.INTL
Days in month DAY EOMONTH
Days until expiration date
Display the current date TODAY
Display the current date and time NOW
Due date by category VLOOKUP WORKDAY XLOOKUP
Dynamic calendar formula WEEKDAY CHOOSE TODAY SEQUENCE DATE
Dynamic calendar grid WEEKDAY CHOOSE TODAY
Dynamic date list TODAY ROWS
Extract date from a date and time INT TRUNC
Extract time from a date and time MOD
Filter on dates expiring soon FILTER SORT AND TODAY
Future time intervals
Get age from birthday DATEDIF TODAY DATE YEARFRAC INT
Get date from day number DATE RIGHT LEFT
Get day from date DAY
Get day name from date TEXT WEEKDAY CHOOSE
Get days before a date TODAY
Get days between dates TODAY DAYS
Get days between dates ignoring years DATEDIF
Get days, hours, and minutes between dates INT TEXT
Get days, months, and years between dates DATEDIF LET
Get earliest and latest project dates MINIFS MAXIFS MIN MAX IF
Get first day of month DAY EOMONTH
Get first day of previous month EOMONTH
Get fiscal quarter from date MONTH CHOOSE
Get fiscal year from date MONTH YEAR
Get last day of month EOMONTH DATE
Get last weekday in month EOMONTH WEEKDAY
Get last working day in month WEEKDAY EOMONTH
Get Monday of the week WEEKDAY
Get month from date MONTH DATE
Get month name from date MONTH CHOOSE TEXT
Get months between dates DATEDIF
Get most recent day of week MOD
Get next day of week MATCH TEXT WEEKDAY
Get next scheduled event MIN IF MINIFS INDEX MATCH
Get nth day of week in month DAY WEEKDAY
Get nth day of year DATE YEAR
Get percent of year complete YEARFRAC YEAR DATE
Get previous Sunday WEEKDAY CHOOSE
Get project end date WORKDAY WORKDAY.INTL
Get project midpoint WORKDAY WORKDAY.INTL
Get project start date WORKDAY WORKDAY.INTL
Get quarter from date MONTH ROUNDUP
Get same date next month EDATE
Get same date next year EDATE
Get week number from date WEEKNUM ISOWEEKNUM
Get work hours between dates NETWORKDAYS NETWORKDAYS.INTL
Get work hours between dates and times NETWORKDAYS NETWORKDAYS.INTL
Get work hours between dates custom schedule MID ROW INDIRECT WEEKDAY SUMPRODUCT
Get workdays between dates NETWORKDAYS NETWORKDAYS.INTL
Get year from date YEAR
Happy birthday message TEXT TODAY IF AND MONTH DAY
If Monday, roll back to Friday WEEKDAY IF
Join date and text TEXT
Last n days AND TODAY
Last n months AND TODAY EOMONTH
Last n weeks AND TODAY WEEKDAY
Last updated date stamp TEXT TODAY
List holidays between two dates TEXTJOIN IF
Month number from name MONTH DATEVALUE
Next anniversary date EDATE DATEDIF
Next biweekly payday from date CEILING
Next business day 6 months in future WORKDAY WORKDAY.INTL
Next working day WORKDAY WORKDAY.INTL
Pad week numbers with zeros TEXT WEEKNUM
Parse time string to time TIME RIGHT LEFT MID
Previous working day WORKDAY WORKDAY.INTL
Remove time from timestamp INT TRUNC
Sum by fiscal year MONTH YEAR SUMPRODUCT SUMIF
Sum race time splits SUM
Sum time SUM SUMIF
Sum time by week and project SUMIFS
Sum time over 30 minutes SUMPRODUCT TIME SUMIFS COUNTIFS
Sum time with SUMIFS SUMIFS
Time difference in hours as decimal value MOD
Time duration with days TIME
Time since start in day ranges IFS IF
Timesheet overtime calculation formula MIN
Total hours that fall between two times MIN MAX
Workdays per month NETWORKDAYS NETWORKDAYS.INTL EOMONTH
Working days in year NETWORKDAYS NETWORKDAYS.INTL DATE TODAY
Working days left in month NETWORKDAYS NETWORKDAYS.INTL TODAY
Year is a leap year DATE YEAR MONTH
#Date series
Formula Related Functions
Biweekly pay schedule SEQUENCE WORKDAY
List nth weekdays of the month LET SEQUENCE FILTER EOMONTH TEXT EDATE
List workdays between dates LET SEQUENCE FILTER WEEKDAY WORKDAY.INTL
Semimonthly pay schedule FILTER SEQUENCE LET DAY EOMONTH WORKDAY
Sequence of custom days WORKDAY.INTL SEQUENCE
Sequence of days SEQUENCE
Sequence of months SEQUENCE EDATE EOMONTH
Sequence of times SEQUENCE TIME
Sequence of weekends WORKDAY.INTL WEEKDAY SEQUENCE
Sequence of workdays SEQUENCE WORKDAY.INTL ROW
Sequence of years SEQUENCE DATE YEAR MONTH DAY
#Text
Formula Related Functions
10 most common text values UNIQUE COUNTIF HSTACK SORT TAKE
Abbreviate names or words TEXTSPLIT TEXTJOIN FILTER MID CODE LEN
Add a line break with a formula CHAR
Add line break based on OS INFO CHAR
Capitalize first letter LEFT MID LEN
Cell begins with LEFT EXACT COUNTIF
Cell contains all of many things SUMPRODUCT ISNUMBER SEARCH COUNTA COUNT
Cell contains number FIND COUNT SEQUENCE MID LEN
Cell contains one of many things SUMPRODUCT ISNUMBER SEARCH
Cell contains one of many with exclusions SUMPRODUCT ISNUMBER SEARCH
Cell contains some words but not others COUNT SEARCH AND
Cell contains specific text SEARCH ISNUMBER FIND
Cell ends with LEFT EXACT COUNTIF
Cell equals one of many things SUMPRODUCT
Clean and reformat telephone numbers SUBSTITUTE
Compare two strings EXACT
Conditional message with REPT function REPT IF AND
Convert numbers to text TEXT
Convert text to numbers VALUE LEFT RIGHT
Count keywords cell contains SUMPRODUCT MATCH ISNUMBER SEARCH
Count line breaks in cell LEN SUBSTITUTE CHAR ISBLANK
Count numbers in text string COUNT SEQUENCE MID LEN INDIRECT ROW
Count specific characters in a range LEN SUBSTITUTE UPPER SUMPRODUCT
Count specific characters in text string LEN SUBSTITUTE LOWER
Count specific words in a cell LEN SUBSTITUTE UPPER
Count specific words in a range LEN SUBSTITUTE UPPER SUMPRODUCT
Count total characters in a cell LEN
Count total characters in a range LEN SUMPRODUCT
Count total words in a cell LEN SUBSTITUTE TRIM
Count total words in a range SUMPRODUCT LEN SUBSTITUTE TRIM
Double quotes inside a formula CHAR
Extract last two words from cell MID LEN SUBSTITUTE FIND
Extract multiple lines from a cell TRIM MID SUBSTITUTE REPT LEN
Extract nth word from text string TEXTSPLIT INDEX TRIM MID SUBSTITUTE REPT LEN
Extract substring MID
Extract text between parentheses MID SEARCH
Extract word containing specific text MID MAX FIND SUBSTITUTE REPT TRIM
Extract word that begins with specific character MID LEN FIND SUBSTITUTE REPT TRIM
Find and replace multiple values SUBSTITUTE INDEX
Find nth occurrence of character SUBSTITUTE FIND CHAR
Get first word LEFT FIND ISERROR
Get last line in cell TRIM SUBSTITUTE RIGHT REPT CHAR
Get last word TRIM SUBSTITUTE RIGHT REPT
Join cells with comma TRIM SUBSTITUTE TEXTJOIN
MAC address format TEXTJOIN SEQUENCE MID
Make words plural IF IFNA VLOOKUP
Most frequent text with criteria INDEX MATCH MODE IF
Most frequently occurring text INDEX MATCH MODE
Normalize text SUBSTITUTE TRIM LOWER
Pad text to equal length REPT LEN
Position of 2nd 3rd etc instance of character SUBSTITUTE FIND
Remove characters from right LEFT LEN VALUE
Remove file extension from filename LEFT FIND
Remove first character REPLACE RIGHT LEN LEFT
Remove last word MID FIND SUBSTITUTE LEN
Remove leading and trailing spaces from text CLEAN TRIM SUBSTITUTE
Remove line breaks SUBSTITUTE CLEAN
Remove text by matching SUBSTITUTE
Remove text by position REPLACE
Remove text by variable position REPLACE FIND
Remove unwanted characters SUBSTITUTE CHAR CODE LEFT CLEAN
Replace one character with another SUBSTITUTE
Replace one delimiter with another TEXTSPLIT TEXTJOIN SUBSTITUTE CHAR
Reverse text string TEXTJOIN MID INDIRECT SEQUENCE
Sort comma separated values TEXTSPLIT SORT TEXTJOIN TRIM FILTERXML
Split dimensions into three parts TEXTSPLIT LEFT RIGHT MID LEN SUBSTITUTE
Split dimensions into two parts SUBSTITUTE LEFT RIGHT FIND
Split numbers from units of measure MAX LEFT RIGHT ISNUMBER VALUE MID
Split text and numbers FIND MIN RIGHT LEFT
Split text string at specific character TEXTBEFORE TEXTAFTER LEFT RIGHT LEN FIND
Split text string to character array MID SEQUENCE LEN
Split text with delimiter TRIM MID SUBSTITUTE REPT LEN
Strip html from text or numbers MID LEN
Strip non-numeric characters TEXTJOIN MID ROW INDIRECT
Strip numeric characters from cell TEXTJOIN MID ROW INDIRECT SEQUENCE LET
Text split to array TEXTSPLIT FILTERXML SUBSTITUTE TRANSPOSE
Translate letters to numbers TEXTJOIN VLOOKUP MID ROW T
Trim text to n words LEFT SUBSTITUTE FIND
#Financial
Formula Related Functions
Annual compound interest schedule FV
Annuity solve for interest rate RATE
Bond valuation example PV FV PRICE
CAGR formula examples GEOMEAN RRI
Calculate compound interest FV
Calculate cumulative loan interest CUMIPMT
Calculate cumulative loan principal payments CUMPRINC
Calculate interest for given period PPMT
Calculate interest rate for loan RATE
Calculate loan interest in given year CUMIPMT
Calculate original loan amount PV
Calculate payment for a loan PMT
Calculate payment periods for loan NPER
Calculate periods for annuity PMT PV FV
Calculate principal for given period PPMT
Calculate simple interest
Compare effect of compounding periods FV
Currency exchange rate example STOCKHISTORY
Effective annual interest rate EFFECT RRI
Estimate mortgage payment PMT PV FV
Future value of annuity FV PV
Future value vs. Present value PV FV
Get current stock price FIELDVALUE STOCKHISTORY
Get stock price (latest close) STOCKHISTORY TODAY LOOKUP INDEX
Get stock price last n days STOCKHISTORY SORT
Get stock price last n months STOCKHISTORY TODAY EDATE INDEX
Get stock price on specific date STOCKHISTORY
Income tax bracket calculation VLOOKUP
Mortgage payment schedule LET SEQUENCE SCAN PMT IPMT PPMT HSTACK VSTACK
NPV formula for net present value NPV
Payment for annuity PMT PV FV
Present value of annuity PV FV
Required recovery rate
Tax rate calculation with fixed base IF
Tax rate calculation with two rates IF
#Geometry
Formula Related Functions
Area of a circle PI POWER
Area of a parallelogram
Area of a trapezoid
Area of a triangle
Circumference of a circle PI
Distance formula SQRT
Pythagorean theorem
Surface area of a cone PI POWER
Surface area of a cylinder PI POWER
Surface area of a sphere PI POWER
Volume of a cone PI POWER
Volume of a cylinder PI POWER
Volume of a rectangular prism
Volume of a sphere PI POWER
#Workbook
Formula Related Functions
Dynamic workbook reference INDIRECT
Dynamic worksheet reference INDIRECT
Get full workbook name and path CELL
Get sheet name only CELL TEXTAFTER MID FIND
Get workbook name and path without sheet CELL LEFT FIND SUBSTITUTE LET
Get workbook name only CELL TEXTAFTER TEXTBEFORE MID FIND
Get workbook path only CELL LEFT FIND LET
Indirect named range different sheet INDIRECT
List sheet index numbers SHEET
List sheet names with formula INDEX MID ROW NOW T
Worksheet name exists ISREF INDIRECT
#Internet
Formula Related Functions
Create email address from name LEFT LOWER CONCAT
Create email with display name CONCAT CONCATENATE TEXTJOIN
Get domain from email address TEXTAFTER TEXTSPLIT RIGHT LEN FIND
Get domain name from URL TEXTAFTER TEXTBEFORE LEFT FIND
Get name from email address TEXTSPLIT TEXTBEFORE LEFT FIND
Get page from URL TEXTAFTER MID LEN FIND
Get top level domain (TLD) TEXTAFTER RIGHT LEN SUBSTITUTE FIND
Remove protocol from URL MID RIGHT LEN FIND
Remove trailing slash from url LEN RIGHT LEFT
#Names
Formula Related Functions
Get first name from name TEXTBEFORE LEFT FIND
Get first name from name with comma TEXTAFTER RIGHT LEN FIND
Get last name from name TEXTAFTER MID LEN SUBSTITUTE FIND
Get last name from name with comma TEXTBEFORE LEFT FIND
Get middle name from full name TEXTAFTER TEXTBEFORE MID FIND IFERROR
Join first and last name TEXTJOIN CONCAT CONCATENATE
Put names into proper case PROPER TRIM TEXTBEFORE TEXTAFTER
Split full name into parts LET TEXTSPLIT INDEX COUNTA DROP TEXTJOIN HSTACK
#Percentage
Formula Related Functions
Calculate percent variance ABS
Calculate percentage of number
Decrease by percentage
Difference is within specific percentage IF ABS
Get amount with percentage
Get original number from percent change
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
Percent sold
Project complete percentage COUNTA
#Random
Formula Related Functions
Random date between two dates RANDBETWEEN WORKDAY
Random number between two numbers RANDBETWEEN
Random number from fixed set of options CHOOSE RANDBETWEEN
Random number weighted probability RAND MATCH INDEX
Random text values CHOOSE RANDBETWEEN
Random times at specific intervals RAND
Random value from list or table INDEX RANDBETWEEN ROWS
Randomly assign data to groups RANDBETWEEN CHOOSE
Randomly assign people to groups RAND ROUNDUP CEILING
#Range
Formula Related Functions
Add row numbers and skip blanks COUNTA IF ISBLANK
Address of first cell in range ADDRESS ROW COLUMN CELL
Address of last cell in range ADDRESS ROW COLUMN ROWS COLUMNS
All cells in range are blank SUMPRODUCT
All values in a range are at least COUNTIF NOT
Automatic row numbers SEQUENCE ROW INDEX COUNTA
Combine ranges with CHOOSE CHOOSE
Count cells in range ROWS COLUMNS COUNTA
Count visible columns CELL N SUM
COUNTIFS with variable range COUNTIFS OFFSET ADDRESS INDIRECT ROW
Define range based on cell value INDEX OFFSET
Dynamic named range with INDEX INDEX COUNTA
Dynamic named range with OFFSET OFFSET COUNTA
Dynamic range between two matches XLOOKUP INDEX MATCH COUNT
First column number in range ROWS MIN
First match between two ranges INDEX MATCH COUNTIF
First row number in range ROW MIN
Get address of named range or table ADDRESS ROW COLUMN ROWS COLUMNS LET TAKE LAMBDA TOCOL
Get relative column numbers in range COLUMN SEQUENCE COLUMNS
Get relative row numbers in range ROW SEQUENCE ROWS
Last column number in range COLUMN COLUMNS MIN
Last n rows ROW ROWS INDEX
Last row in mixed data with blanks MATCH
Last row in mixed data with no blanks COUNTA
Last row in numeric data MATCH
Last row in text data MATCH REPT
Last row number in range ROW ROWS MIN
Multiple cells are equal AND EXACT
Multiple cells have same value COUNTIF
Multiple cells have same value case sensitive EXACT SUMPRODUCT COUNTA
Multiple columns are equal AND COUNTIF
Range contains a value not in another range SUMPRODUCT MATCH ISNA
Range contains numbers SUMPRODUCT ISNUMBER
Range contains specific date COUNTIFS DATE TODAY
Row is blank SUMPRODUCT BYROW LAMBDA
Total columns in range COLUMNS
Total rows in range ROWS
#Tables
Formula Related Functions
Automatic row numbers in Table ROW INDEX
Average last N values in a table INDEX AVERAGE ROWS
Basic inventory formula example SUMIFS
Count table columns COLUMNS
Count table rows ROWS
COUNTIFS with variable table column COUNTIFS INDEX MATCH INDIRECT
Dynamic reference to table INDIRECT
Get column index in Excel Table MATCH
Get column name from index in table INDEX
Percentile IF in table PERCENTILE
Running count in Table INDEX SUM
Running total in Table INDEX SUM
Sum multiple tables SUM
SUMIFS vs other lookup formulas SUMIFS INDEX MATCH LOOKUP XLOOKUP SUMPRODUCT
SUMIFS with Excel Table SUMIFS
Two-way lookup VLOOKUP in a Table VLOOKUP MATCH
Two-way summary with SUMIFS SUMIFS
#Errors
Formula Related Functions
How to fix a circular reference error
How to fix the #### (hashtag) error
How to fix the #CALC! error IFERROR ISERROR ERROR.TYPE
How to fix the #DIV/0! error IFERROR ISERROR ERROR.TYPE
How to fix the #N/A error VLOOKUP IFERROR MATCH
How to fix the #NAME? error IFERROR ISERROR ERROR.TYPE
How to fix the #NULL! error IFERROR ISERROR ERROR.TYPE
How to fix the #NUM! error IFERROR ISERROR ERROR.TYPE
How to fix the #REF! error ISREF IFERROR
How to fix the #SPILL! error IFERROR ISERROR ERROR.TYPE
How to fix the #VALUE! error IFERROR ISERROR ERROR.TYPE
#Miscellaneous
Formula Related Functions
Abbreviate state names VLOOKUP INDEX MATCH
Add leading zeros to numbers TEXT
All dates in chronological order IF SUMPRODUCT SORT
Basic array formula example MAX MIN
Basic attendance tracking formula COUNTIF
Basic error trapping example IFERROR
Basic in cell histogram REPT CHAR
Basic numeric sort formula RANK COUNTIF
Basic outline numbering COUNTA MID FIND LEN
Basic text sort formula RANK COUNTIF
BMI calculation formula CONVERT POWER
Build hyperlink with VLOOKUP HYPERLINK VLOOKUP
Calculate a ratio from two numbers GCD
Calculate win loss tie totals SUMPRODUCT
Cap percentage at 100 MIN
Cap percentage at specific amount MIN
Carry-on baggage Inches to centimeters CONVERT
Cash denomination calculator SUMPRODUCT INT FLOOR
Celsius to Fahrenheit conversion CONVERT
Change negative numbers to positive ABS
Check register balance IF AND ISBLANK
Coefficient of variation STDEV.P STDEV.S AVERAGE
Conditional median with criteria MEDIAN
Conditional mode with criteria MODE
Convert column letter to number COLUMN INDIRECT
Convert column number to letter ADDRESS SUBSTITUTE TEXTBEFORE
Convert expense time units INDEX MATCH
Convert feet and inches to inches LEFT FIND MID SUBSTITUTE
Convert inches to feet and inches INT MOD TRUNC ABS
Convert negative numbers to zero MAX
Convert numbers to 1 or 0 IF
Convert pounds to kilograms CONVERT
Copy value from every nth column OFFSET COLUMN
Copy value from every nth row OFFSET ROW
Cost of living adjustment
Count consecutive monthly orders FREQUENCY MAX IF
Count values out of tolerance SUMPRODUCT ABS
Count with repeating values ROUNDUP COLUMN ROW
Course completion status summary COUNTIFS
Course completion summary with criteria COUNTIFS
Create array of numbers INDIRECT ROW
Cube root of number POWER
Customer is new COUNTIFS
Display sorted values with helper column INDEX MATCH ROWS
Dropdown sum with all option SUMIF SUM IF
Easy bundle pricing with SUMPRODUCT SUMPRODUCT
Expense begins on specific month IF
Extract unique items from a list INDEX MATCH COUNTIF LOOKUP
Filter values in array formula ISNUMBER MATCH IF COUNT
Fixed value every N columns MOD COLUMN
Flag first duplicate in a list COUNTIF COUNTIFS
Flip table rows to columns TRANSPOSE
Forecast vs actual variance SUMIFS
Formula with locked absolute reference INDIRECT
Get date associated with last entry LOOKUP
Get first entry by month and year INDEX MATCH TEXT
Get last entry by month and year LOOKUP TEXT
Get pivot table grand total GETPIVOTDATA
Get pivot table subtotal GETPIVOTDATA
Get pivot table subtotal grouped date GETPIVOTDATA
Hyperlink to first blank cell HYPERLINK CELL INDEX MATCH
Hyperlink to first match HYPERLINK CELL INDEX MATCH
Increment a calculation with ROW or COLUMN ROW COLUMN
Increment a number in a text string RIGHT TEXT
Increment cell reference with INDIRECT INDIRECT CELL
Leave a comment in a formula N
Link to multiple sheets HYPERLINK CELL INDEX MATCH
List most frequently occuring numbers MODE ISNUMBER MATCH
Longest winning streak FREQUENCY MAX IF
Lookup last file revision SEARCH ISERROR MAX INDEX IF ROW
Mark rows with logical tests COUNTIFS IF
Most frequently occurring number MODE
Multiplication table formula
New customers per month COUNTIFS EOMONTH
Nightly hotel rate calculation SUMPRODUCT FILTER INDEX MATCH
Normalize size units to Gigabytes MATCH LEFT RIGHT
nth root of number POWER
Number is whole number MOD TRUNC INT
Odometer gas mileage log SUM MAX MIN
One or the other not both XOR
Pad a number with zeros TEXT REPT
Parse XML with formula FILTERXML
Random sort formula INDEX MATCH
Range contains duplicates OR COUNTIF SUMPRODUCT
Range contains one of many substrings SUMPRODUCT COUNTIF
Range contains one of many values SUMPRODUCT ISNUMBER SEARCH
Range contains specific text COUNTIF
Repeat fixed value every 3 months MOD DATEDIF
Return array with INDEX function INDEX MATCH N
Reverse a list or range INDEX COUNTA ROW SORTBY SEQUENCE
Risk Matrix Example INDEX MATCH
Score quiz answers with key SUM COUNTA
Search entire worksheet for value COUNTIF
Search multiple worksheets for value COUNTIF INDIRECT
Send email with formula HYPERLINK
Show formula text with formula FORMULATEXT IFERROR ISFORMULA
Simple currency conversion VLOOKUP IF
Sort and extract unique values MMULT TRANSPOSE INDEX MATCH
Sort numbers ascending or descending SMALL LARGE
Sort text and numbers with formula RANK COUNTIF COUNT
Split payment across months AND
Square root of number SQRT POWER ABS
Standard deviation calculation STDEV.P STDEV.S STDEV STDEVP
Student class enrollment with table IF COUNTIF
Sum every 3 cells OFFSET COLUMN
Sum Roman numbers ARABIC ROMAN SUMPRODUCT SUM
Sum text values like numbers INDEX MATCH N
Text is greater than number COUNTIF COUNTIFS SUMPRODUCT ISNUMBER
Transpose table without zeros TRANSPOSE IF
Unwrap column into fields OFFSET TRANSPOSE ROW
Validate input with check mark IF COUNTIF
Value exists in a range COUNTIF MATCH ISNUMBER
Value is between two numbers AND MAX MIN
Value is within tolerance IF ABS
Volunteer hours requirement calculation AND COUNTIF SUM
Back to Top