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
Concatenate cell values based on a condition [No VBA]
Concatenate unique distinct values
Lookup and return multiple values concatenated into one cell
Use a drop down list to extract and concatenate unique distinct values

Back to top
Compare
Automate Excel: Update list with new values
Compare tables: Filter records occurring only in one table
Compare two columns and return 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 values between two columns and filter values existing in only one column
Create a list with most recent data available
Extract shared values between two columns
Filter common values between two ranges
Filter common values from three separate columns
Filter shared records from two tables
Filter values occurring in range 1 but not in range 2
Filter values that exists in all three lists
How to extract not shared values in 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 cells based on a condition and month
Count cells between specified values
Count cells between two values
Count cells containing text from list
Count colored cells
Count contiguous values
Count digits and ignore duplicates
Count entries based on date and time
Count groups in calendar
Count groups of each value
Count identical numbers in two columns but items must be in same row
Count overlapping days across multiple date ranges
Count overlapping days in multiple date ranges
Count overlapping days in multiple date ranges, part 2
Count students
Count unique distinct months
Count unique distinct numbers across multiple sheets
Count unique distinct records with a date and column criteria
Count unique distinct values
Count unique distinct values based on a condition
Count unique distinct values in a filtered table
Count unique distinct values in two columns
Count unique distinct values that meet multiple criteria
Count unique distinct values within same week, month or year
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 values based on a date
Inventory consumption
Most frequent value between two dates
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 entries based on date and time
Count groups in calendar
Count overlapping days across multiple date ranges
Count overlapping days in multiple date ranges
Count overlapping days in multiple date ranges, part 2
Create a date range [Formula]
Create a list of dates with blanks between quarters
Create date ranges: Weeks within a month
Days contained in a range that overlap another range
Extract dates from overlapping date ranges
Extract unique distinct values based on a date range
Extract unique distinct year and months from dates
Filter duplicate values and sort by corresponding date
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 missing dates in a set of date ranges
Formula for matching a date within a date range
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 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 count overlapping time
Identify overlapping date ranges
Identify rows of overlapping records
Inventory consumption
List all unique events in a month
List dates outside specified date ranges
Lookup min max values within a date range
Lookup the nearest date
Most frequent value between two dates
Prevent users from entering overlapping date and time ranges
Search a table and use the returning value to search another table
Use MEDIAN function to calculate overlapping ranges
Working with overlapping date ranges

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
Extract a list of duplicates from two columns combined
Extract duplicate values with exceptions
Extract duplicates from a range
Filter duplicate text values in a range using “begins with” criterion
Filter duplicate values and sort by corresponding date
Filter duplicate values in a range using “contain” condition
Filter duplicate values using critera
Filter duplicates within same date, week or month
Find min and max unique and duplicate numerical values

Back to top
Duplicate records
Extract duplicate records

Back to top
Back to top
Search and return multiple values
A record is returned if both search strings are found on the same row
Find the most urgent work orders
How to search for a string in a column
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 multiple search strings
Search each column for a string each and return multiple records – AND logic
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 column – AND logic

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

Back to top
Sort values
Create a unique distinct sorted list containing both numbers text removing blanks
Extract a list of alphabetically sorted duplicates from a column
Filter unique values sorted from A to Z
How to sort a data set in a custom order
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 and criteria
Sort based on frequency row-wise
Sort cell values in corresponding columns
Sort column based on frequency
Sort dates within a date range
Sort names by value
Sort numeric values based on proximity to a given number
Sort records based on two columns
Sort text cells alphabetically from two columns
Sort text values by length
Sort two columns
Sort values by corresponding text
Sort values in a cell using a custom delimiter [VBA]
Sort values in an Excel table [VBA]

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 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 OR – AND logic
Sum by group
Sum cells containing numbers and text based on a condition
Sum cells with check boxes
Sum only visible cells
Sum unique numbers
Sum values between two dates and based on a condition

Back to top
Text string manipulation
How to replace part of formula in all cells
Identify all characters in a cell value

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
Use VLOOKUP to calculate discount percentages

Back to top
Unique values
5 easy ways to extract Unique Distinct Values
Create numbers based on numerical ranges
Extract unique values from two columns
Filter unique values and sort based on adjacent date
Filter unique values from a cell range
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
Sum number based on corresponding unique value

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 unique distinct alphabetically sorted list
Create a unique distinct list based on criteria
Create a unique distinct list where a corresponding column has text cell values
Create unique distinct list sorted based on text length
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 distinct text values containing string in a range
Extract unique distinct values based on a date range
Extract unique distinct values based on the 4 last characters
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Extract unique distinct values from a multi-column cell range
Extract unique distinct values if value contains string
Extract unique distinct values sorted based on sum of adjacent values
Extract unique distinct values that begins with a given string from a cell range
Extract unique distinct year and months from dates
Filter unique distinct values where adjacent cells contain search string
Filter unique distinct values, sorted and blanks removed from a range
How to create a unique distinct list based on two conditions
List all unique events in a month
Remove duplicates based on date
Remove duplicates within same month or year
Sum unique distinct invoices
Unique distinct list from a column sorted A to Z
Unique distinct values sorted based on frequency
Vlookup – Return multiple unique distinct values

Back to top
Unique distinct records
Count unique distinct records (rows) in a Pivot Table
Count unique distinct records with a date and column criteria
Extract unique distinct records based on a criterion
Extract unique distinct records from two data sets
Filter unique distinct records
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
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 a cell range and return multiple values
Vlookup across multiple sheets
VLOOKUP in a filtered Excel Table and return multiple values
Vlookup with 2 or more lookup criteria and return multiple matches
Vlookup with multiple matches returns a different value

Back to top