How to use the SUMIFS function

The SUMIFS function in cell D11 adds numbers from column D based on criteria applied to column B and C. […]

How to use the SUMIFS function

The SUMIFS function in cell D11 adds numbers from column D based on criteria applied to column B and C. […]

How to use the CHOOSE function

The CHOOSE function lets you get a value based on a number, the number determines which value to get. The […]

INDEX and MATCH – multiple criteria and multiple results

This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]

If cell contains multiple values

This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 […]

How to ignore zeros using the SMALL function

This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]

SMALL function – multiple conditions

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

How to use the AVERAGE function

Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and […]

How to extract numbers from a cell value

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]

SUMPRODUCT and nested IF functions

I have demonstrated in a previous postÂ how to simplify nested IF functions, in this article I will show you how […]

List all unique distinct rows in a given month

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

How to use the UNIQUE function

The UNIQUE function is a very versatile Excel function, it lets you extract both unique and unique distinct values and […]

Filter unique distinct values based on a date range

Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]

SMALL function ignore duplicates

This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

SUMPRODUCT – multiple criteria

This article demonstrates ways to sum values based on criteria. Table of Contents SUMPRODUCT - based on a list of […]

Partial match for multiple strings – AND logic

This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]

Unique distinct records sorted based on count or frequency

This article demonstrates how to sort a table based on count meaning the formula counts each record and returns a […]

Find earliest and latest overlapping dates in a set of date ranges based on a condition

This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]

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

Partial match based on two conditions

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Perform multiple partial matches and return records – AND logic

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Partial match and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Partial match values and return value with highest level

This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]

INDEX MATCH with multiple criteria

This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]

Get the smallest number larger than a given number

This article demonstrates how to extract the smallest number larger than a condition and the largest number smaller than a […]

Match a range value containing both text and numerical characters

This article demonstrates how to match a value containing both text and digits to ranges. The search value is specified […]

How to quickly find the maximum or minimum value [Formula]

This article demonstrates formulas that will return the largest and smallest numbers, the corresponding column headers, the cell addresses, and […]

Sort text cells alphabetically from two columns

Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

Sort column based on frequency

This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]

INDEX MATCH – multiple results

This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]

Compare two columns and highlight differences

This article demonstrates a conditional formatting formula that will highlight the differences between two columns. The image above shows two […]

If cell equals value from list

This article demonstrates formulas that check if a cell value is equal to any value in a given list. Table […]

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

Identify the position of a value in an array.

How to use the STOCKHISTORY function

The STOCKHISTORY function lets you get stock prices based on a stock quote. It is in the Lookup and reference […]

List permutations no repetition [UDF]

This blog post describes how to createÂ permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]

How to use the SUBTOTAL function

The picture above shows row 4 and row 8 hidden, the SUM argument 9 sums all values in C3:C11 whereas […]

How to use the SEQUENCE function

The SEQUENCE function creates a list of sequential numbers to a cell range or array. It is located in the […]

How to use the FILTER function

The FILTER function lets you extract values/rows based on a condition or criteria.Â It is in the Lookup and reference category […]

Partial match with two conditions and return multiple results

This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

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

How to simplify nested IF functions

Nested IF statements in a formula are multipleÂ combined IF functions so more conditions and outcomes become possible. They all are […]

How to use the HYPERLINK function

The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, […]

Create links to all sheets in a workbook programmatically

The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet.Â You will then be able to quickly […]

Create new worksheets programmatically based on values in a cell range [VBA]

This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

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

Gets a value in a specific cell range based on a row and column number.

Use IF + COUNTIF to evaluate multiple conditions

The image above demonstrates a formula that matches a value to multiple conditions, if the condition is met the formula […]

Extract a unique distinct list and ignore blanks

This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]

How to use the FREQUENCY function

Returns how many times values exist in a given range. Note, this function returns an array of values.

If cell contains text from list

This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]

Search for a text string in a data set and return multiple records

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Formula for matching a date within a date range

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

Find latest date based on a condition

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

How to do tiered calculations in one formula

The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

Distribute values across numerical ranges

This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

How to use the COUNTIF function to count not blank cells

The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]

Find empty cells and sum cells above

This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]

Rearrange values using formulas

The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]

Opening a workbook runs a macro automatically

This article explains how to set up a workbook so a macro is run every time you open the workbook. […]

Run a Macro from a Drop Down list [VBA]

This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

How to use the XMATCH function

This article demonstrates the XMATCH function introduced in Excel 365. The XMATCH function is an enhanced version of the MATCH […]

How to extract a case sensitive unique list from a column

This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]

Sort and return unique distinct single digits from cell range

This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

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

Lookup based on a date range and a condition return multiple values

This article demonstrates a formula that extracts values from a column based on a date range criteria and another condition. […]

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

How to use the SEARCH function

Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.

Filter duplicate values using criteria

This article demonstrates formulas that extract duplicates based on three conditions. Table of Contents Filter duplicate values using criteria How […]