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
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 price lists
Compare two columns and show differences
Compare two columns for same values [Excel Formula]
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 lists of data: Highlight common records
Compare two lists of data: Highlight records occurring in only one list
Compare two tables: Filter common records
Compare two tables: Remove common rows
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
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 existing only in one out of two ranges [UDF]
Filter values in common between two cell ranges [UDF]
Filter values occurring in range 1 but not in range 2
Filter values that exists in all three lists
Find the most urgent work orders
Highlight duplicate values in two cell ranges combined
Highlight unique distinct values in two cell ranges
How many common cell values are there in list 1 and 2?
How to compare two data sets
How to highlight differences in price lists
How to identify two consecutive dates in a list
How to remove common values between two columns
What values are missing in List 1 that exists i List 2?

Back to top
Count values
Auto populate a sheet
Calculate frequency the past 90 days
Count a specific weekday in a date range
Count cell blocks
Count cells based on a condition and month
Count cells by cell and font color
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 in any line
Count entries between date and time criteria
Count how many numbers between a specific value occurring multiple times
Count how many times a string exists in a cell range (case insensitive)
Count identical numbers in two columns but items must be in same row
Count matching cell values in two columns
Count matching values in one or more columns
Count multiple text strings in a cell range
Count per row
Count records between two dates with multiple parameters
Count specific text string in a cell
Count students
Count text string in a range (case sensitive)
Count the number of cells within a range that match multiple comma separated values
Count the number of groupings of each value
Count the number of times a specific number exists in a list
Count unique and duplicate numbers in a closed workbook
Count unique and duplicates text values in a closed workbook
Count unique and unique distinct values in a multicolumn 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 [UDF]
Count unique distinct values in a large dataset with a date criterion
Count unique distinct values in an Excel Pivot Table
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

Back to top
Dates
Calculate dates in each step in a project based on a finish date
Calculate frequency the past 90 days
Calculate machine utilization
Calculate min and max date among overlapping date ranges and based on a condition
Calculate time between time zones
Convert date ranges into dates
Convert dates into date ranges
Count a specific weekday in a date range
Count cell blocks
Count cells based on a condition and month
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 records between two dates with multiple parameters
Count unique distinct values based on date criteria in a range
Count unique records between two dates
Create a list of dates with blanks between quarters in excel
Create a unique distinct list from a date range
Create random dates, Mon to Fri, within a year in excel
Date ranges: Weeks within a month
Days contained in a range that overlap another range
Extract dates and adjacent value in a range using a date critera in excel
Extract dates using a drop down list in excel
Extract unique distinct year and months from dates
Filter duplicate rows and sort by date using array formula in excel
Filter duplicates within same date, week or month in excel
Filter overlapping date ranges in excel 2007
Filter overlapping dates from date ranges in excel
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 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
Highlight date ranges overlapping selected record [VBA]
Highlight duplicate values and overlapping dates in excel
Highlight duplicates with same date, week or month
Highlight events overlapping federal holidays
Highlight odd/even months
Highlight overlapping date ranges using conditional formatting
How many of a specific weekday falls between a start date and an end date except holidays
How to automatically calculate a specific day of a month
How to calculate missing months in a given date range
How to count overlapping time
How to create random numbers, text strings, dates and time values

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 using array formula in excel
Extract duplicate text values from a range containing both numerical and text values
Extract duplicate values with exceptions
Extract duplicates and their adjacent values
Extract duplicates from a range using excel array formula
Filter duplicate rows and sort by date using array formula in excel
Filter duplicate text values in a range using “begins with” criterion in excel
Filter duplicate values in a range using “contain” condition in excel
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 using array formula in excel
Filter duplicates within same date, week or month in excel
Filter unique distinct and duplicate values from a large data set in excel 2007
Find min and max unique and duplicate numerical values
How to extract a list of duplicates from a column in excel
Remove duplicates from a large dataset [UDF]
Remove duplicates in same week in excel
Remove duplicates on same date in excel
Remove duplicates within same month or year
Validate duplicates in excel

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
Lookup with multiple criteria and return multiple search results
Multiple wildcard lookups and include or exclude criteria
Nested Search
Quickly highlight records containing text strings in excel (AND Logic)
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]
Repeat values
Sequencing and numbering of batches

Back to top
Sort values
Count per row
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 negative values and adjacent cells in excel
Extract numbers and text from a range sorted from A to Z
Extract unique distinct values from a filtered table [udf and array formula]
Filter duplicates from two columns combined and sort from A to Z using array formula in excel
Filter unique values sorted from A to Z
Find max unique value from a range that have duplicate numbers and blanks
How to filter numbers inside (and outside) number ranges in excel
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 in excel
Lookup and return multiple values sorted in a custom order
Match a criterion and extract multiple corresponding table headers
Reverse a list ignoring blanks in excel
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 using array formula in excel
Sort a table with an array formula
Sort cell values in corresponding columns
Sort dates within a date range
Sort names by value
Sort numeric values by closest to farthest to a specific number in excel (array formula)
Sort text cells alphabetically from two columns using excel array formula
Sort text values by length using array formula in excel
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
Find empty cells and sum cells above
How to use the SUM function
Running totals
Sum based on multiple criteria
Sum between two dates
Sum by group
Sum cells with check boxes
Sum only visible cells
Sum text cells using criteria
Sum values between two dates and based on a condition

Back to top
Text string manipulation
Remove duplicate text strings based on the 4 last characters

Back to top
Two dimensional lookup
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 how many numbers between a specific value occurring multiple times
Count unique and duplicate numbers in a closed workbook
Count unique and duplicates text values in a closed workbook
Count unique records between two dates
Count unique text values in a range containing both numerical and text values
Count unique values in two lists combined in excel
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
Highlight unique values and unique distinct values in a cell range
How to extract a case sensitive unique list from a column
How to filter numbers inside (and outside) number ranges in excel
List all unique events in a month
Lookup Unique based on Multiple Conditions
Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
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
Count cells by cell and font color
Count unique and duplicate numbers in a closed workbook
Count unique and duplicates text values in a closed workbook
Count unique distinct text values in a range
Count unique distinct values based on date criteria in a range
Count unique distinct values by cell color
Count unique distinct values in a filtered table
Count unique distinct values in a large dataset [UDF]
Count unique distinct values in a large dataset with a date criterion
Count unique distinct values in an Excel Pivot Table
Count unique records between two dates
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 based on criteria
Create a unique distinct list from a date range
Create a unique distinct list using Advanced Filter in a macro [VBA]
Create a unique distinct list where a corresponding column has text cell values
Extract a unique distinct list and sum amounts based on a condition
Extract a unique distinct list based on a condition [Pivot Table]
Extract a unique distinct list from three columns
Extract a unique distinct list from two columns
Extract a unique distinct list sorted from A-Z from range
Extract unique distinct numbers from closed workbook in excel (formula)
Extract unique distinct text list from a range containing both numerical and text values
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 a unique distinct list and remove blanks
Filter unique distinct and duplicate values from a large data set in excel 2007
Filter unique distinct text values in a range using “contain” condition in excel
Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
Filter unique distinct values (case sensitive) [Excel UDF]
Filter unique distinct values from two ranges combined in excel 2007
Filter unique distinct values if value contains specific string [Formula and Advanced Filter]
Filter unique distinct values where adjacent cells contain search string
Filter unique distinct words from a cell range [UDF]
Highlight unique distinct values in two cell ranges
Highlight unique values and unique distinct values in a cell range
How to count unique distinct occurrences for each date
How to create a unique distinct list where other columns meet two criteria
List all unique events in a month
Match a criterion and extract multiple corresponding table headers
Merge matching rows in excel (text values)
Remove duplicates from a large dataset [UDF]
Remove duplicates in same week in excel
Remove duplicates on same date in excel
Remove duplicates within same month or year

Back to top
Unique distinct records
Count unique distinct records in Excel
Count unique distinct records with a date and column criteria
Excel 2007 pivot table: Count unique distinct records (rows)
Excel table: Filter unique distinct rows and use additional filters at the same time
Filter unique distinct records
Filter unique distinct records (case sensitive)
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 a range and return multiple corresponding values
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 – Return multiple unique distinct values
Vlookup a cell range and return multiple values
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