Basic Formulas | Advanced Formulas

 

Advanced Formula Categories
Concatenate Search cells Unique values
Compare Sequence Unique distinct values
Count Sort Unique distinct records
Dates Sum Vlookup
Duplicate values Text string manipulation
Duplicate records Two-dimensional lookup
Concatenate
Concatenate cell values based on a condition [No VBA]
Concatenate unique distinct values
Lookup and return multiple values concatenated into one cell
Quickly concatenate values into one cell [No VBA]
Use a drop down list to extract and concatenate unique distinct values

Back to top
Compare
Compare two columns and show differences
Compare two columns in different worksheets
Compare two lists and filter unique values where the sum in one column doesn’t match the other column
Compare two lists of data: Filter records occurring in only one list
Compare two tables: Filter common records
Compare two tables: Remove common records
Compare values between two columns and filter values existing in only one column
Comparing two columns and sum unique values
Create a list with most recent data available
Extract shared values between two columns
Filter common text values in two different ranges
Filter common values between two ranges
Filter common values from three columns
Filter text values existing in the first range but not in the 2nd range
Filter values occurring in range 1 but not in range 2
Filter values that exists in all three lists
Highlight duplicate values in two cell ranges combined
How many common cell values are there in list 1 and 2?
How to compare two data sets
How to extract not common values between two columns
How to highlight differences in price lists
What values are missing in List 1 that exists i List 2?

Back to top
Count values
Count a given pattern in a cell value
Count a specific weekday in a date range
Count cell blocks
Count cells based on a condition and month
Count cells between a given value
Count cells between two values
Count cells containing text from list
Count colored cells
Count contiguous values
Count date records between two dates in a range
Count digits and ignore duplicates
Count entries between date and time criteria
Count groups of each value
Count identical numbers in two columns but items must be in same row
Count matching cell values in two columns
Count overlapping days across multiple date ranges
Count overlapping days in multiple date ranges
Count overlapping days in multiple date ranges, part 2
Count past 90 days based on a condition
Count records between two dates with multiple parameters
Count students
Count unique and unique distinct numbers in a multi-column range
Count unique distinct months
Count unique distinct numbers across multiple sheets (3D range
Count unique distinct records with a date and column criteria
Count unique distinct text values in a range
Count unique distinct values
Count unique distinct values based on a condition
Count unique distinct values based on date criteria in a range
Count unique distinct values in a filtered table
Count unique distinct values in a large dataset with a date criterion
Count unique distinct values in three columns combined
Count unique distinct values in two columns
Count unique distinct values in two columns with date criteria
Count unique distinct values that meet multiple criteria
Count unique distinct values within same week, month or year
Count unique records between two dates
Count unique text values in a range containing both numerical and text values
Count unique values and unique distinct values in three ranges combined
Count unique values and unique distinct values in two ranges combined
Count unique values in two lists combined in excel
How many common cell values are there in list 1 and 2?
How many of a specific weekday falls between a start date and an end date except holidays
How to count overlapping time
How to count unique distinct occurrences for each date
Inventory consumption
Most common value between two dates in a range
Most frequent value between two dates
Return value if it exists more then n times
Sort based on frequency row-wise

Back to top
Dates
Calculate machine utilization
Calculate min and max date among overlapping date ranges and based on a condition
Calculate project dates based on a finish date
Convert date ranges into dates
Convert dates into date ranges
Count a specific weekday in a date range
Count cell blocks
Count date records between two dates in a range
Count entries between date and time criteria
Count overlapping dates
Count overlapping days across multiple date ranges
Count overlapping days in multiple date ranges
Count overlapping days in multiple date ranges, part 2
Count past 90 days based on a condition
Count records between two dates with multiple parameters
Create a date range [Formula]
Create a list of dates with blanks between quarters
Create a unique distinct list from a date range
Create date ranges: Weeks within a month
Create random dates, Mon to Fri, within a year
Days contained in a range that overlap another range
Extract dates from overlapping date ranges
Extract unique distinct year and months from dates
Filter duplicate rows and sort by date using array formula
Filter duplicates within same date, week or month
Filter overlapping date ranges
Filter weeks from a date range
Find consecutive dates in a cell range
Find date given day and week
Find date range
Find latest date based on a condition
Find latest date in a list
Find missing dates in a set of date ranges
Formula for matching a date within a date range
Generate list of random dates with criterion
Get date ranges from a schedule
Highlight date ranges overlapping selected record [VBA]
Highlight duplicates with same date, week or month
Highlight events overlapping federal holidays
Highlight odd/even months
Highlight overlapping date ranges using conditional formatting
Highlight records based on overlapping date ranges and a condition
How many of a specific weekday falls between a start date and an end date except holidays
How to calculate a date based on specific weekday in a month
How to calculate missing months in a given date range
How to count overlapping time
How to highlight MAX and MIN value based on month
How to sort cells filtered by two dates
Identify overlapping date ranges
Identify rows of overlapping records

Back to top
Duplicate values
Create a list of duplicates where adjacent cell value meets a condition
Easily identify groups of duplicate rows
Extract a list of alphabetically sorted duplicates based on a condition
Extract a list of alphabetically sorted duplicates from a column
Extract a list of duplicates from a column
Extract a list of duplicates from three columns combined using array formula in excel
Extract a list of duplicates from two columns combined
Extract duplicate text values from a range containing both numerical and text values
Extract duplicate values with exceptions
Extract duplicates from a range using excel array formula
Filter duplicate rows and sort by date using array formula
Filter duplicate text values in a range using “begins with” criterion
Filter duplicate values in a range using “contain” condition
Filter duplicate values using critera
Filter duplicate words from a cell range [UDF]
Filter duplicates from two columns combined and sort from A to Z
Filter duplicates within same date, week or month
Filter unique distinct and duplicate values from a large data set
Find min and max unique and duplicate numerical values
How to extract a list of duplicates from a column in excel
Remove duplicates based on week
Validate duplicates

Back to top
Duplicate records
Extract duplicate records
Filter duplicate records

Back to top
Back to top
Search and return multiple values
Extract records where all criteria match if not empty
Find the most urgent work orders
How to search for a string in a cell range
Lookup with multiple criteria and return multiple search results
Multiple wildcard lookups and include or exclude criteria
Nested Search
Return multiple matches with wildcard vlookup
Search and display all cells that contain all search strings in excel
Search each column for a string each and return multiple records – OR logic
Search for a text string and return multiple adjacent values
Search for multiple text strings in multiple cells
Search for multiple text strings in multiple cells in excel, part 2
Split search value using delimiter and search for each substring

Back to top
Sequence
Create number sequences
Find a sequence
Find a sequence of values – wildcard search
Find the longest/shortest consecutive sequence of a value
Find the longest/smallest consecutive sequence of a value [VBA]
How to identify two consecutive dates in a list
Repeat values
Sequencing and numbering of batches

Back to top
Sort values
Extract a list of alphabetically sorted duplicates from a column
Extract and sort text cells from a range containing both numerical and text values
Extract numbers and text from a range sorted from A to Z
Filter duplicates from two columns combined and sort from A to Z
Filter unique values sorted from A to Z
How to sort a table in a custom order in excel [No formula]
How to sort cells filtered by two dates
List five smallest numbers, excluding zeros
List people with the highest scores based on criteria
Lookup and return multiple values sorted in a custom order
Reverse a list ignoring blanks
Sort a column alphabetically
Sort a list in random order in excel
Sort a range based on value frequency
Sort a range from A to Z [Array formula]
Sort a table with an array formula
Sort based on frequency row-wise
Sort cell values in corresponding columns
Sort dates within a date range
Sort names by value
Sort numbers inside (and outside) number ranges
Sort numeric values by proximity to a given number
Sort text cells alphabetically from two columns
Sort text values by length
Sort values by corresponding text
Sort values in a cell using a custom delimiter [VBA]
Sort values in an Excel table [VBA]
Sort values in parallel (array formula)
Sort values in parallel in excel, part 2

Back to top
Sum function
Count cells containing text from list
Count how many times a string exists in a cell range (case insensitive)
Count multiple text strings in a cell range
Count overlapping dates
Count overlapping days in multiple date ranges
Count text string in a range (case sensitive)
Count unique distinct values
How to use the SUM function
Running totals
Sum based on multiple criteria
Sum by group
Sum cells with check boxes
Sum numbers between two dates
Sum only visible cells
Sum values between two dates and based on a condition
Sum values containing text based on a condition

Back to top
Text string manipulation
How to replace part of formula in all cells

Back to top
Two dimensional lookup
Get date ranges from a schedule
How to perform a two-dimensional lookup
How to use the INDEX function
Reverse two-way lookups
Shift Schedule
Two-dimensional lookup using two tables
Two-way lookup using multiple tables [UDF]
Use VLOOKUP to calculate discount percentages

Back to top
Unique values
5 easy ways to extract unique distinct values
Comparing two columns and sum unique values
Count cells between a given value
Count unique text values in a range containing both numerical and text values
Count unique values in two lists combined in excel
Extract unique and duplicate numbers from multiple sheets
Extract unique values from a range
Extract unique values from two columns
Filter unique text values from a range containing both numerical and text values
Filter unique text values in a range using “contain” condition in excel
Filter unique text values using “begins with” criterion in a range in excel
Filter unique values and sort based on adjacent date
Filter unique values from a range using array formula in excel
Filter unique values sorted from A to Z
Filter unique words from a range [UDF]
Find min and max unique and duplicate numerical values
How to extract a case sensitive unique list from a column
List all unique events in a month
Sort numbers inside (and outside) number ranges
Sum number based on if a corresponding value is unique
Sum unique values in excel
Validate unique list in excel

Back to top
Unique distinct values
5 easy ways to extract unique distinct values
Assign each person with randomly unique objects as a daily activity
Compare two lists and filter unique values where the sum in one column doesn’t match the other column
Create a list of distinct values from a list where an adjacent cell value meets a criteria
Create a unique distinct alphabetically sorted list
Create a unique distinct list and sort based on frequency
Create a unique distinct list based on criteria
Create a unique distinct list from a date range
Create a unique distinct list where a corresponding column has text cell values
Extract a unique distinct list and remove blanks
Extract a unique distinct list and sum amounts based on a condition
Extract a unique distinct list from three columns
Extract a unique distinct list from two columns
Extract a unique distinct list sorted alphabetically and ignore blanks from a range
Extract a unique distinct list sorted from A-Z from range
Extract unique and duplicate numbers from multiple sheets
Extract unique distinct text list from a range containing both numerical and text values
Extract unique distinct values based on the 4 last characters
Extract unique distinct values from a filtered table [udf and array formula]
Extract unique distinct values from a multi-column cell range
Extract unique distinct year and months from dates
Filter unique distinct and duplicate values from a large data set
Filter unique distinct text values containing string in a range
Filter unique distinct text values that begins with criterion
Filter unique distinct values from two ranges combined
Filter unique distinct values if value contains specific string [Formula and Advanced Filter]
Filter unique distinct values where adjacent cells contain search string
How to create a unique distinct list based on two conditions
List all unique events in a month
Merge matching rows (text values)
Remove duplicates based on date
Remove duplicates within same month or year
Search each column for a string each and return multiple records – OR logic
Sum unique distinct invoices
Unique distinct list from a column sorted A to Z
Validate unique distinct list
Vlookup – Return multiple unique distinct values
Vlookup a cell range and return multiple values

Back to top
Unique distinct records
Count unique distinct records
Count unique distinct records with a date and column criteria
Excel 2007 pivot table: Count unique distinct records (rows)
Extract unique distinct records based on a criterion
Extract unique distinct records from two data sets
Filter unique distinct records
Filter unique distinct records (case sensitive)
Filter unique distinct records using criteria
Filter unique distinct records with a condition

Back to top
Vlookup and return multiple values
5 easy ways to VLOOKUP and return multiple values
Lookup and return multiple values on the same row from a range excluding blanks
Lookup multiple values in different columns and return multiple values
Lookup values in a range based on criteria and return multiple values
Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
Search for a cell value in a dataset
Search values distributed horizontally and return corresponding value
Unique distinct list sorted alphabetically based on a condition
Use a drop down list to search and return multiple values
Use VLOOKUP and return multiple values sorted from A to Z
Vlookup across multiple sheets
Vlookup with 2 or more lookup criteria and return multiple matches
Vlookup with multiple matches returns a different value

Back to top