Count cells containing text from list
Table of Contents Count cells containing text from list Count entries based on date and time 1. Count cells containing […]

Match two columns and return another value on the same row
This article demonstrates formulas that match two conditions in a column each and return another value on the same row […]

Count unique distinct values based on a condition
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

Extract a list of alphabetically sorted duplicates based on a condition
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

Working with overlapping date ranges
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

Search for a sequence of cells based on wildcard search
This article demonstrates array formulas that perform a wildcard search based on a sequence of values. The formulas return the […]

Search for a sequence of values
This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above […]

How to group items by quarter using formulas
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]

Merge tables based on a condition
This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]

Identify rows of overlapping records
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

Prevent duplicate records in a worksheet
This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]

Assign records unique random text strings
This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]

How to use the COUNTIFS function
Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas – use named ranges
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

Working with Relational Tables in Excel
Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]

Lookup multiple values across columns and return a single value
Table of Contents Lookup multiple values across columns and return a single value Lookup using multiple conditions Lookup a date […]

Unique distinct records sorted based on count or frequency
This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]

Auto populate a worksheet
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]

Working with Conditional Formatting formulas
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]

Watch schedule that populates vacation time
This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]

Label groups of duplicate records
Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

Extract unique distinct values in a filtered list
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

Remove common records between two data sets
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

How to compare two data sets
Table of Contents How to compare two data sets - Excel Table and autofilter Filter shared records from two tables […]

How to highlight differences and common values in lists
This article demonstrates techniques to highlight differences and common values across lists. What's on this page How to highlight differences […]

Compare tables: Filter records occurring only in one table
Table of Contents Compare tables: Filter records occurring only in one table Compare two lists and filter unique values where […]

Sort rows based on frequency and criteria
Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]

Extract dates from overlapping date ranges
The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]

Convert date ranges into dates
This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]

Match two criteria and return multiple records
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

How to return a value if lookup value is in a range
In this article, I will demonstrate four different formulas that allow you to lookup a value that is to be found […]

VLOOKUP/XLOOKUP of three columns to pull a single record
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Table of Contents VLOOKUP […]

Extract a list of duplicates from a column
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Calculate machine utilization
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]

Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

Find closest value
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]