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 […]

SMALL function – multiple conditions
This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]

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 […]

IF function with AND function – multiple conditions
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]

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 […]

Find empty dates in a set of date ranges
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]

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 […]

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.

Working with three relational tables
I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]

Extract unique distinct values from a relational table
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]

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

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 […]

Filter overlapping date ranges
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]

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 […]

Create numbers based on numerical ranges
Table of Contents Create numbers based on numerical ranges - Excel 365 Create numbers based on numerical ranges - earlier […]

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 […]

List dates outside specified date ranges
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

Filter duplicates within same date, week or month
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same 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 […]

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 […]