#Count
Formula Related Functions
Count between dates by age range COUNTIFS FIND LEFT RIGHT SUMPRODUCT TEXTBEFORE LEN
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 SUM ROW
Count cells that end with COUNTIF
Count columns that contain specific values MMULT COLUMN TRANSPOSE BYCOL LAMBDA SUM ROW
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 MONTH
Count if two criteria match COUNTIFS SUMPRODUCT
Count items in list COUNTIFS
Count long numbers SUMPRODUCT COUNTIF SUM
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 INDIRECT
Count or sum variance SUMPRODUCT SUM ABS
Count or sum whole numbers only MOD SUMPRODUCT
Count paired items in listed combinations COUNTIFS CONCAT IF
Count rows that contain specific values MMULT COLUMN TRANSPOSE BYROW LAMBDA SUM
Count rows with at least n matching values MMULT COLUMN TRANSPOSE SUM
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 INT COUNTIF SUMPRODUCT
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 ROW MIN
COUNTIF with non-contiguous range COUNTIF INDIRECT VSTACK SUM
COUNTIFS with multiple criteria and OR logic COUNTIFS SUM
Histogram with FREQUENCY FREQUENCY
Running count of occurrence in list COUNTIF IF LET SCAN
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 ISNUMBER MATCH
Two-way summary count COUNTIFS LET UNIQUE HSTACK VSTACK
#Sum
Formula Related Functions
Calculate running total SUM SCAN LAMBDA
Count cells that contain formulas SUMPRODUCT ISFORMULA NOT
Subtotal by color SUMIF COUNTIF
Subtotal by invoice number SUMIF COUNTIF IF
Subtotal invoices by age SUMIFS 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 SEQUENCE
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 ROW
Sum every nth column SUM FILTER SEQUENCE SUMPRODUCT COLUMN MOD COLUMNS
Sum every nth row FILTER SEQUENCE MOD SUM SUMPRODUCT ROW ROWS
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 FILTER SUM GROUPBY BYROW
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 with multiple ranges SUMIFS LET VSTACK GROUPBY CHOOSECOLS
Sum if x or y SUMIFS SUM SUMIF SUMPRODUCT ISNUMBER MATCH
Sum last 30 days SUMIFS TODAY SUMPRODUCT FILTER
Sum last n columns TAKE OFFSET COLUMNS INDEX SUM TRIMRANGE
Sum last n rows TAKE SUM TRIMRANGE 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 VALUE
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 ISNUMBER
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 CHOOSECOLS
Combine ranges VSTACK HSTACK INDEX SEQUENCE ROWS COLUMNS LET
Count unique values UNIQUE COUNTA COUNT ISTEXT
Count unique values with criteria UNIQUE FILTER LEN SUM COUNTA
Detailed LET function example LET VLOOKUP IF XLOOKUP
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 SUMIFS UNIQUE TRANSPOSE AVERAGEIFS
Extract common values from text strings TEXTSPLIT FILTER XMATCH ISNUMBER LET SORT TEXTJOIN
Extract common values from two lists FILTER XMATCH ISNUMBER UNIQUE SORT COUNTIF
Extract numbers from text TEXTSPLIT TOROW DROP
Filter and exclude columns FILTER SORT CHOOSECOLS TAKE
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 YEAR DATE
FILTER case-sensitive FILTER SEARCH ISNUMBER EXACT
Filter contains one of many FILTER ISNUMBER MATCH
Filter data between dates FILTER DATE
Filter every nth row FILTER SEQUENCE MOD ROW ROWS
Filter exclude blank values FILTER
Filter horizontal data FILTER TRANSPOSE
FILTER last n valid entries FILTER SEQUENCE INDEX SORT SUM COLUMNS
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 IF
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 optional criteria FILTER LET SEQUENCE ROWS IF ISNUMBER SEARCH GROUPBY
FILTER with partial match FILTER SEARCH ISNUMBER
Generate random text strings SORTBY RANDARRAY COUNTA SEQUENCE CHAR TEXTJOIN INDEX
Get column totals BYCOL LAMBDA MMULT ROW TRANSPOSE SEQUENCE SUM
Get row totals BYROW LAMBDA MMULT COLUMN TRANSPOSE SEQUENCE SUM
GROUPBY with monthly totals GROUPBY HSTACK CHOOSECOLS TEXT MONTH SUMPRODUCT EOMONTH
GROUPBY with survey results GROUPBY PERCENTOF HSTACK SORTBY XMATCH CHOOSECOLS
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 SEQUENCE CODE LEN TEXTJOIN IF ABS CHAR
LAMBDA strip trailing characters recursive LAMBDA MID LEN
List all combination lock codes SEQUENCE TEXT REPT LET HSTACK INT MOD INDEX ROWS PERMUTATIONA
List all combinations SEQUENCE INDEX FILTER LET BYROW DROP AND LAMBDA INT MOD ROWS COMBINA COMBIN
List all combinations from multiple lists SEQUENCE INDEX HSTACK LET INT MOD ROWS
List all permutations of a list SEQUENCE INDEX FILTER LET BYROW UNIQUE COUNT LAMBDA TOCOL HSTACK VSTACK REDUCE INT MOD ROWS PERMUT FACT
List upcoming birthdays SORTBY LET TEXT INDEX SEQUENCE XMATCH TODAY EDATE MIN ROWS COLUMNS
MAP with AND and OR logic MAP AND OR LAMBDA
Minimum value if unique UNIQUE MIN COUNTIF IF
Random list of names TAKE SORTBY RANDARRAY ROWS INDEX CHOOSEROWS
Random numbers without duplicates SEQUENCE SORTBY RANDARRAY INDEX
Random sort SORTBY RANDARRAY ROWS
Remove blank rows FILTER BYROW LAMBDA SUM
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
Split number into digits SEQUENCE INT MOD LET BASE DECIMAL
Sum numbers with text SUM TEXTBEFORE TEXTAFTER UNIQUE LET LAMBDA BYROW
Summarize weather data by day and month GROUPBY HSTACK MAP DROP CHOOSECOLS LAMBDA LET XLOOKUP EOMONTH INT TEXT
TEXTSPLIT get numeric values TEXTSPLIT VALUE IFERROR LET
Tiered discounts based on quantity MAP VSTACK DROP IF MIN MAX LET LAMBDA
Unique rows UNIQUE SORT
Unique values by count UNIQUE FILTER COUNTIF
Unique values case-sensitive REDUCE LAMBDA EXACT VSTACK IF SUM
Unique values from multiple ranges UNIQUE VSTACK
Unique values ignore blanks UNIQUE FILTER
Unique values with criteria UNIQUE FILTER
UNIQUE with non-adjacent columns FILTER UNIQUE SORT
Values that appear only once UNIQUE COUNTIF SORT FILTER
XLOOKUP match any column XLOOKUP MMULT SEQUENCE COLUMNS
#Min and Max
Formula Related Functions
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 EDATE
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 LARGE MAX UNIQUE IF
nth smallest value SMALL
nth smallest value with criteria SMALL FILTER
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
Fuzzy match noisy data GROUPBY XLOOKUP XMATCH FILTER UNIQUE VLOOKUP MATCH ISNA
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 TOCOL IF ADDRESS ROW COLUMN MAP NA
Get nth match FILTER INDEX CHOOSEROWS SMALL IF MIN ROW
Get nth match with INDEX / MATCH INDEX SMALL IF ROW MIN
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 IF
INDEX and MATCH all partial matches FILTER 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 INDEX MATCH MMULT COLUMN TRANSPOSE
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 XMATCH ISNA
Look up and return to single cell FILTER TEXTJOIN GROUPBY LAMBDA UNIQUE ARRAYTOTEXT
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 IF
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 COLUMN
Multi-criteria lookup and transpose INDEX MATCH
Multiple chained VLOOKUPs VLOOKUP IFERROR
Multiple matches in comma separated list TEXTJOIN IF
Multiple matches into separate columns FILTER UNIQUE SORT SEQUENCE INDEX SMALL IFERROR COLUMN COLUMNS TRANSPOSE
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
Step-based lookup example INDEX MATCH
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 IF
VLOOKUP from another sheet VLOOKUP XLOOKUP
VLOOKUP from another workbook VLOOKUP
VLOOKUP if blank return blank VLOOKUP IF
VLOOKUP override output VLOOKUP IF
VLOOKUP tax rate calculation VLOOKUP
VLOOKUP two-way lookup VLOOKUP MATCH
VLOOKUP variable commission split VLOOKUP
VLOOKUP with 2 lookup tables VLOOKUP IF
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 LET
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 MAX INDEX MATCH
XLOOKUP horizontal lookup XLOOKUP HLOOKUP
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 XLOOKUP FILTER LEFT MONTH NOT
XLOOKUP with logical criteria XLOOKUP
XLOOKUP with multiple criteria XLOOKUP INDEX MATCH
XLOOKUP with regex match XLOOKUP
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 IF 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 SUM
IF with other calculations IF AND
IF with wildcards IF COUNTIF SEARCH ISNUMBER
Invoice age and status IF IFS 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 SUM
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
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 ROWS ROW INDEX
Find duplicate values in two columns COUNTIF AND
Gantt chart AND
Gantt chart by week AND
Gantt chart time schedule AND OR IF
Gantt chart with weekends WEEKDAY
Highlight 3 smallest values with criteria SMALL AND IF
Highlight approximate match lookup conditional formatting LOOKUP OR AND
Highlight blank cells ISBLANK LEN
Highlight bottom values SMALL
Highlight cells that begin with SEARCH COUNTIF FIND
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 OR AND NOT
Shade alternating groups of n rows ROW CEILING ISEVEN ISODD
#Data validation
Formula Related Functions
Data validation allow letters and numbers only LET FIND MID LOWER SEQUENCE ISNUMBER AND REGEXTEST SUMPRODUCT LEN
Data validation allow numbers only ISNUMBER
Data validation allow text only ISTEXT
Data validation allow uppercase only UPPER EXACT AND ISTEXT
Data validation allow weekday only WEEKDAY 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 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 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 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 number to n significant figures LET ROUND LOG10 INT ABS IF TEXT REPT
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 WORKDAY.INTL
Add days exclude certain days of week WORKDAY.INTL
Add days to date TODAY EDATE WORKDAY
Add decimal hours to time TIME MOD
Add decimal minutes to time TIME MOD
Add months to date EDATE EOMONTH
Add workdays to date custom workweek WORKDAY.INTL WORKDAY
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 hours between two times IF MOD
Calculate retirement date EDATE YEARFRAC SIGN
Calculate time before expiration date IF DATEDIF
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
Convert UTC timestamp to Excel datetime SUBSTITUTE TEXTSPLIT TIME SUM
Count birthdays by month SUMPRODUCT MONTH
Count calls at specific times COUNTIFS
Count dates in current month COUNTIFS EOMONTH SUMPRODUCT N TODAY
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 IF AND MONTH YEAR TEXT
Date is workday WORKDAY WORKDAY.INTL
Days in month DAY EOMONTH
Display the current date TODAY
Display the current date and time NOW
Due date by category VLOOKUP WORKDAY XLOOKUP
Dynamic calendar formula SEQUENCE WEEKDAY TODAY DATE
Dynamic calendar grid WEEKDAY CHOOSE TODAY
Dynamic date list TODAY ROWS
Extract date from a date and time INT TRUNC
Extract date from text string MID SEARCH REGEXEXTRACT
Extract time from a date and time MOD INT TRUNC TIME ROUND HOUR MINUTE SECOND
Filter on dates expiring soon FILTER SORT AND TODAY
Future time intervals
Generate quarter dates EDATE SEQUENCE EOMONTH
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 TODAY
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 YEAR MONTH
Get last weekday in month EOMONTH WEEKDAY
Get last working day in month WORKDAY 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 YEARFRAC MONTH YEAR
Get most recent day of week MOD
Get next day of week MATCH TEXT WEEKDAY
Get next scheduled event MIN IF MINIFS INDEX MATCH TODAY
Get nth day of week in month WEEKDAY MOD LET EOMONTH MONTH NA
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 SEQUENCE WEEKDAY MID LET XMATCH INDEX
Get work hours between dates and times NETWORKDAYS NETWORKDAYS.INTL IF MEDIAN MOD
Get workdays between dates NETWORKDAYS NETWORKDAYS.INTL
Get year from date YEAR
Happy birthday message TEXT TODAY IF AND MONTH DAY
Hours that overlap specific time blocks MIN MAX LET LAMBDA INT MAP SEQUENCE
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 XMATCH DATEVALUE LEFT
Next anniversary date EDATE DATEDIF
Next biweekly payday from date CEILING
Next business day 6 months in future WORKDAY WORKDAY.INTL EDATE
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 duration with days TIME
Time in hundredths of a second TIME SECOND TIMEVALUE MOD
Time since start in day ranges IFS IF
Timesheet overtime calculation formula MIN
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 EOMONTH
Year is a leap year DATE YEAR MONTH MOD AND OR
#Date series
Formula Related Functions
Biweekly pay schedule SEQUENCE WORKDAY
List all dates in a month SEQUENCE EOMONTH DAY LET
List nth weekdays of the month LET SEQUENCE FILTER EOMONTH TEXT EDATE TODAY BYROW LAMBDA SUM
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 leap years SEQUENCE LET FILTER MOD BYROW AND OR
Sequence of months SEQUENCE EDATE EOMONTH
Sequence of times SEQUENCE TIME ROUND
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 LET
Abbreviate names or words TEXTSPLIT TEXTJOIN FILTER MID CODE LEN SEQUENCE LET LEFT
Add a line break with a formula CHAR TEXTJOIN
Add line break based on OS INFO CHAR IF
Capitalize first letter in a text string REPLACE UPPER 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 REGEXTEST
Cell contains specific words TEXTSPLIT XMATCH COUNT COUNTA LET
Cell ends with RIGHT EXACT COUNTIF
Cell equals one of many things SUMPRODUCT TRIM
Clean and reformat telephone numbers TEXTSPLIT TEXTJOIN SUBSTITUTE SEQUENCE
Compare two strings EXACT
Conditional message with REPT function REPT IF AND
Convert numbers to text TEXT
Convert text to numbers VALUE LEFT RIGHT REGEXREPLACE ISTEXT ISNUMBER
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 TEXTSPLIT COUNTA LEN SUBSTITUTE TRIM
Count total words in a range SUMPRODUCT LEN SUBSTITUTE TRIM
Double quotes inside a formula CHAR
Encode Unicode sequence into text UNICHAR TEXTSPLIT TEXTJOIN HEX2DEC
Extract last two words from cell MID LEN SUBSTITUTE FIND
Extract multiple lines from a cell TRIM MID SUBSTITUTE REPT LEN CHAR
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 LEFT
Find and replace multiple values SUBSTITUTE REDUCE SEQUENCE LAMBDA REGEXREPLACE INDEX LET ROWS TRIM
Find nth occurrence of character SUBSTITUTE FIND TEXTBEFORE LEN
Get first word LEFT FIND ISERROR
Get last line in cell TRIM SUBSTITUTE RIGHT REPT CHAR
Get last word TRIM SUBSTITUTE RIGHT REPT
Get Unicode Sequence from text UNICODE REGEXEXTRACT BASE TEXTJOIN
Join cells with comma TEXTJOIN SUBSTITUTE TRIM
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
Number to words LET LAMBDA MOD MAP INT INDEX CHOOSE ISOMITTED SEQUENCE
Pad text to equal length REPT LEN
Remove characters from right LEFT LEN REGEXREPLACE REPLACE
Remove file extension from filename LEFT FIND
Remove first character REPLACE RIGHT LEN LEFT
Remove last word TEXTBEFORE TRIM MID FIND SUBSTITUTE LEN
Remove leading and trailing spaces from text CLEAN TRIM SUBSTITUTE
Remove line breaks SUBSTITUTE CLEAN CHAR
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 TRIM
Reverse text string CONCAT MID SEQUENCE LEN TEXTJOIN ROW INDIRECT
Sort comma separated values TEXTSPLIT SORT TEXTJOIN TRIM FILTERXML
Split comma-separated values to multiple columns TEXTSPLIT REDUCE LAMBDA DROP FILTERXML VSTACK
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 TEXTSPLIT TEXTBEFORE TEXTAFTER LEFT RIGHT LEN FIND
Split text string to character array REGEXEXTRACT MID SEQUENCE LEN
Split text with delimiter TRIM MID SUBSTITUTE REPT LEN
Strip html from text or numbers MID LEN
Strip non-numeric characters REGEXREPLACE TEXTJOIN SEQUENCE MID LEN ROW INDIRECT
Strip numeric characters from cell TEXTJOIN MID ROW INDIRECT SEQUENCE LET IF ISERR
Translate letters to numbers XLOOKUP CONCAT MID SEQUENCE LEN TEXTJOIN SUM
Trim text to n words LEFT SUBSTITUTE FIND
Validate strong password REGEXTEST COUNT FIND LEN UPPER ISERROR
#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 Coast FI number PV FV NPER PMT
Calculate compound interest FV
Calculate cumulative loan interest CUMIPMT
Calculate cumulative loan principal payments CUMPRINC
Calculate interest for given period IPMT 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 NPER PMT PV FV
Calculate principal for given period PPMT IPMT
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 TODAY
Get stock price last n months STOCKHISTORY TODAY EDATE INDEX TRANSPOSE
Get stock price on specific date STOCKHISTORY
Income tax bracket calculation IF LET VSTACK DROP XLOOKUP XMATCH FILTER OFFSET CHOOSECOLS
Monthly savings needed to reach goal PMT FV
Mortgage payment schedule LET SEQUENCE SCAN FILTER 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
Simple investing worksheet FV
Tax rate calculation with fixed base IF
Tax rate calculation with two rates IF
Time needed to reach savings goal NPER FV ROUNDUP
#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 SQRT
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
Count errors in all sheets SUMPRODUCT ISERROR INDIRECT HYPERLINK ISREF
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 TEXTBEFORE
Get workbook name only CELL TEXTAFTER TEXTBEFORE MID FIND
Get workbook path only TEXTBEFORE CELL LEFT FIND
Indirect named range different sheet INDIRECT SUM
List sheet index numbers SHEET INDIRECT
List sheet names with formula TEXTAFTER TRANSPOSE 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 TEXTAFTER 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 IFS
#Percentage
Formula Related Functions
Calculate percent variance ABS AVERAGE
Calculate percentage of number
Decrease by percentage
Difference is within specific percentage IF ABS
Get original number from percent change
Get original price from percentage discount
Get percentage discount
Get percentage of total
Get profit margin percentage
Increase by percentage
Percent of goal
Project complete percentage COUNTIF COUNTA
#Random
Formula Related Functions
Random date between two dates RANDBETWEEN DATE WORKDAY
Random number between two numbers RANDBETWEEN RANDARRAY
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 FLOOR RAND
Random value from list or table INDEX RANDBETWEEN ROWS
Randomly assign data to groups RANDBETWEEN CHOOSE RANDARRAY ROWS
Randomly assign people to groups INDEX RAND RANK ROUNDUP SEQUENCE RANDARRAY SORTBY LET ROWS COUNTA
#Range
Formula Related Functions
Add row numbers and skip blanks SCAN IF SUM SUMPRODUCT COUNTA
Address of first cell in range ADDRESS ROW COLUMN CELL MIN
Address of last cell in range ADDRESS ROW COLUMN ROWS COLUMNS MAX
All cells in range are blank COUNTA SUMPRODUCT
All values in a range are at least COUNTIF NOT
Automatic row numbers SEQUENCE ROWS COUNTA TRIMRANGE XMATCH ROW INDEX
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 SUM
Dynamic named range with INDEX INDEX COUNTA TRIMRANGE
Dynamic named range with OFFSET OFFSET COUNTA TRIMRANGE
Dynamic range between two matches XLOOKUP INDEX MATCH COUNT SUM
First column number in range COLUMN 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 TAKE SORTBY SEQUENCE 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 AND
Multiple cells have same value case sensitive EXACT SUMPRODUCT COUNTA AND
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 SUM
Get column index in Excel Table MATCH
Get column name from index in table INDEX
Percentile IF in table PERCENTILE IF
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 IF 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 MAX MEDIAN IF
Cap percentage at specific amount MIN IF
Carry-on baggage Inches to centimeters CONVERT
Cash denomination calculator SUMPRODUCT INT LET SCAN MOD DROP HSTACK SUM
Celsius to Fahrenheit conversion CONVERT NOT ISBLANK
Change negative numbers to positive ABS
Coefficient of variation STDEV.P STDEV.S AVERAGE
Conditional median with criteria MEDIAN FILTER IF
Conditional mode with criteria MODE IF
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 ABS LEN
Convert inches to feet and inches INT MOD LET LAMBDA ROUND ABS MROUND TEXT
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 COLUMN
Count values out of tolerance SUMPRODUCT ABS
Count with repeating values ROUNDUP COLUMN ROW
Course completion status summary COUNTIFS IF
Course completion summary with criteria COUNTIFS
Create array of numbers SEQUENCE ROW INDIRECT
Cube root of number POWER
Customer is new COUNTIFS
Display sorted values with helper column INDEX MATCH ROWS
Drag formula down step column reference OFFSET ROWS INDEX TRANSPOSE COLUMNS SUM
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 IF
Flag first duplicate in a list COUNTIF COUNTIFS IF
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 SUM
Link to multiple sheets HYPERLINK CELL INDEX MATCH
List most frequently occurring numbers MODE ISNUMBER MATCH IF
Longest winning streak SCAN FREQUENCY MAX IF LET FILTER VSTACK DROP LAMBDA
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 LEN
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 ROWS
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 IF
Repeat range of values SEQUENCE MOD CHOOSEROWS CHOOSECOLS
Repeat sequence of numbers SEQUENCE MOD
Return array with INDEX function INDEX MATCH N SUM IF
Reverse a list or range ROWS ROW SORTBY SEQUENCE TEXTSPLIT TEXTJOIN REDUCE INDEX
Risk Matrix Example INDEX MATCH
Running balance with debits and credits SCAN SUM LAMBDA TRIMRANGE INDEX LET
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 numbers ascending or descending SMALL LARGE ROWS
Sort text and numbers with formula RANK COUNTIF COUNT ISTEXT
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 SUM OFFSET COLUMN
Sum Roman numbers ARABIC ROMAN SUMPRODUCT SUM
Sum text values like numbers SUM INDEX MATCH N IF
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 IF
Value is between two numbers AND MAX MIN
Value is within tolerance IF ABS
Volunteer hours requirement calculation AND COUNTIF SUM

Support Exceljet

Many websites (including Exceljet) have lost over half their traffic in the past year because of the crushing impact of AI on web searches. Exceljet is still at it, publishing free content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever. Plus, you get our Excel Shortcuts video course as a thank you.

Get the Lifetime Pass