IF function


Excel » Functions » Logical » IF function »

How to find the smallest number excluding zeros

Column B contains numbers, the formula in cell D3 calculates the smallest value excluding zeros. Note that all numbers are […]

Count complete hours between two times

The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]

Get date ranges from a schedule

The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]

How to replace part of formula in all cells

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]

If cell contains any text

The picture above shows different values in column B and a formula in column C that tries to identifies the […]

Count cells with text

The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]

Get the latest revision

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]

How to remove unwanted characters in a cell

Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]

Extract first word in cell

The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character. =LEFT(B3,SEARCH(" ",B3)-1) […]

AVERAGE based on criteria

This article demonstrates two formulas that calculate averages, the first formula calculates an average based on criteria, and the second […]

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

SMALL function for text

This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]

AVERAGE ignore NA()

The AVERAGE function ignores empty cells, text values, and boolean values automatically, however, it doesn't handle error values. The AVERAGE […]

AVERAGE ignore blanks

Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]

SMALL function ignore duplicates

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

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

SUMPRODUCT and IF function

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]

INDEX MATCH – multiple results

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

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

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

IF with OR function

This article shows examples of how to use the IF function with the OR function. Table of Contents IF with […]

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

If cell contains text

This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]

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

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

Two-dimensional lookup using two tables

The following formula performs a two-way lookup in two different tables.

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

How to create running totals

This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]

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

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

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

Concatenate cell values based on a condition [No VBA]

Add cell values to a single cell with a condition, no VBA in this article.

Unique distinct list sorted alphabetically based on a condition

This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]

Use VLOOKUP and return multiple values sorted from A to Z

This article demonstrates how to extract multiple values based on a search value and display them sorted from A to […]

Concatenate unique distinct values

This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]

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

Reverse two-way lookups

This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]

Split search value using delimiter and search for each substring

This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

Rotating unique groups with no repeat

This article demonstrates a formula What's on this page Question Formula Array formula Explaining array formula Get the Excel File […]

Time sheet for work

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]

How to extract rows containing digits [Formula]

This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]

Calendar – monthly view

This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]

How to calculate and plot pivots on an Excel chart

  If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]

Compare two columns in different worksheets

This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]

Sort values by corresponding text arranged in a column

This article describes a formula that sorts values arranged in a column from A to z by every other value. […]

Follow stock market trends – trailing stop

This time I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post […]

Follow stock market trends – Moving Average

In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]

Find and return the highest number and corresponding date based on a condition

This article describes how to filter records based on the maximum value of a specific item. There are names in […]

How to color chart bars based on their values

(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]

Sort based on frequency row-wise

In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

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

Dynamic scoreboard

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

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

Split expenses calculator

This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]

How to count repeating values

This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]

Find numbers closest to sum

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Extract the most repeated adjacent values in a column

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A.  Cell […]

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

Working with classic ciphers in Excel

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

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

Highlight unique values in a filtered Excel table

This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]

Lookup value based on two critera – second criteria is the adjacent value and its position in a given list

This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]

Lookup and return multiple sorted values based on corresponding values in another column

This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

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

Compare two columns and extract differences

This article demonstrates a formula that extracts values that exist only in one column out of two columns. There are […]

Wildcard lookups and include or exclude criteria

This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]

Lookup with any number of criteria

This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]

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

How to select and delete blank cells

This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]

Pivot Table calendar

This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]

Practice basic arithmetic calculations in Excel

This article demonstrates a workbook that allows children to practice basic mathematics or more specifically arithmetic calculations. The image above […]

Create a hyperlink linked to the result of a two-dimensional lookup

The image above shows a formula in cell C2 that searches for a value based on two conditions specified in […]

Find all sequences of consecutive dates

The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]

Highlight a data series in a line chart

This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]

Highlight duplicates in a filtered Excel Table

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

How to use the IF function

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Convert array formula to a regular formula

This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]

Highlight a bar in a chart

This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]

Repeat values across cells

This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]

VLOOKUP and return multiple values across columns

This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]

Find smallest and largest unique number

This article explains how to calculate the largest and smallest number based on a condition which is if the number […]

How to use VLOOKUP with multiple conditions

I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]

Locate lookup values in an Excel table [HYPERLINK]

Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]

Highlight lookups in relational tables

This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add […]

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

Change column/bar color in charts

This article demonstrates how to set up a chart so it shows one color for increasing bars/columns and another color […]

Lookups in relational tables

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

How to create name initials

The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

Search related table based on a date and date range

I will in this article demonstrate how to search a table for a date based on a condition and then […]

Excel calendar [VBA]

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

Populate drop down list with filtered Excel Table values

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

Use drop down lists and named ranges to filter chart values

This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]

Shift Schedule

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

Plot date ranges in a calendar part 2

I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]

Count unique distinct values in a filtered Excel defined Table

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

Change chart axis range programmatically

This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]

Extract table headers based on a condition

This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]

Plot buy and sell points in an Excel Chart based on two moving averages

This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]

Add buy and sell points to a stock chart

The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]

Create date ranges that stay within month

This article demonstrates a formula that creates date ranges based on a given number of days and the end date […]

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

Use a drop down list to filter and concatenate unique distinct values

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]

Count a specific weekday in a date range

NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

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

Schedule project dates based on a finish date

Danielle asks: I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes […]

Automate data entry [VBA]

This article demonstrates how to automatically enter data in cells if an adjacent cell is populated using VBA code. In […]

How to create random numbers, text strings, dates and time values

The RAND() function In Excel returns a number greater than or equal to 0 (zero) and less than 1. Combining […]

Dependent drop-down lists in multiple rows

This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]

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

Find entry based on conditions

Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department. I have a […]

Free School Schedule Template

This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and 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 […]

Basic invoice template

Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

Extract week ranges based on a given date range

The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]

Use a drop down list to search and return multiple values

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

Count weekday within date range except holidays

Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]

Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

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

Count cells between specified values

This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]

VLOOKUP in a filtered Excel Table and return multiple values

This post describes how to search filtered values in an Excel defined Table using a condition given in cell 12 and return […]

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

Filter unique distinct values, sorted and blanks removed from a range

EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

Vlookup across multiple sheets

This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.

Search each column for a string each and return multiple records – OR logic

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Count unique distinct records with a date and column criteria

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

Calculate running average of last 10 data with random blank cells

Question: List of data and blank cells in a column which will be added from day to day. There are […]

Bill reminder in excel

Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]

Filter records based on a date range and a text string

Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]

Fuzzy vlookup [Array formula]

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

Text to columns: Split words in a cell

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

Highlight records [AND logic]

The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]

Merge matching rows

Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]

Filter overlapping date ranges

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

Count digits and ignore duplicates

Question: I have a question that I can’t seem to find an answer to: I want to make a full […]

Tracking a stock portfolio #2

This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]

Create a drop down calendar

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]

Weekly appointment calendar

This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]

Monthly calendar template #2

I have created another monthly calendar template for you to get. Select a month and year in cells A1 and […]

Compare tables: Filter records occurring only in one table

In this example we are going to use two lists with identical columns, shown in the image above. It is […]

Filter shared records from two tables

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

Extract duplicate records

This article describes how to filter duplicate rows with the use of a formula.  It is, in fact, an array […]

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

Automate Excel: Update list with new values

Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]

Return multiple matches with wildcard vlookup

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

Compare two columns and return differences

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]

Apply dependent combo box selections to a filter

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

Create a drop down list containing alphabetically sorted values

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]

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

True round-robin tournament

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

Create a random playlist

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

Case sensitive lookup and return multiple values

The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]

Vlookup with multiple matches returns a different value

Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above […]

Extract dates from a cell block schedule

Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]

Populate cells dynamically in a weekly schedule

In this post I am going to add one more function to the weekly schedule I built in a previous […]

Create numbers based on numerical ranges

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]

Find empty hours in a weekly schedule

The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

Schedule recurring expenses in a calendar in excel (Personal Finance)

Below is an excel table containing recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to […]

Extract a unique distinct list sorted from A to Z ignore blanks

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Extract dates from overlapping date ranges

The array formula in cell B3 extracts overlapping dates based on the date ranges in column D and E. Array […]

Convert date ranges into dates

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

Convert dates into date ranges

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

List dates outside specified date ranges

The Excel defined table contains start and end dates for each date range in cell range A3:B10. Cell B13 is […]

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

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

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

Count unique distinct values within same week, month or year

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]

How to count unique distinct values based on a date

The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]

Remove duplicates within same month or year

The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same […]

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

Automate net asset value (NAV) calculation on your stock portfolio

Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]

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

Extract records containing negative numbers

Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]

Create number series

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

Return multiple values if in range

The image above shows a formula in cell C11 that extracts values from column D if the number in cell […]

Sort two columns

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Filter duplicate values in a range using “contain” condition

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]

Extract unique distinct text values containing string in a range

The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]

Filter duplicate values from a range that begins with string

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]

Extract unique distinct values from cell range that begins with string

The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]

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

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

Search values distributed horizontally and return corresponding value

Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]

Most frequent value between two dates

In this article I will show you how to extract the most frequent value (text or number) between two dates […]

Lookup two index columns

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Create a quartely date range

I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Extract unique distinct values sorted based on sum of adjacent values

Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]

Filter unique values from a cell range

Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]

Filter unique values sorted from A to Z

A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]

Create a list with most recent data available

Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]

Extract records where all criteria match if not empty

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

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

Lookup with criteria and return records.

Create unique distinct list sorted based on text length

The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]

Unique distinct values sorted based on frequency

What's on this page Unique distinct values sorted based on frequency Unique distinct values sorted based on frequency - Excel […]

Unique distinct list from a column sorted A to Z

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]

Identify missing numbers in a range

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

Extract numbers from a column

I this article I will show you how to get numerical values from a cell range manually and using an […]

Extract unique distinct values A to Z from a range and ignore blanks

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

Create a list of dates with blanks between quarters

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

How to highlight MAX and MIN value based on month

The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]

Identify missing three character alpha code numbers

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

Find min and max unique and duplicate numerical values

Table of Contents Extract largest duplicate number Extract largest duplicate number - Excel 365 Extract smallest duplicate number Extract smallest […]

Reverse a list ignoring blanks

This article demonstrates two different formulas, one for Excel 365 and one for earlier versions. Table of Contents Reverse a […]

List values with past date

Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]

Sum cells containing numbers and text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

Sort a range based on value frequency

The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

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

Filter common values between two ranges

The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

Filter values occurring in range 1 but not in range 2

The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]

Filter duplicate values and sort by corresponding date

Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]

Sort a range from A to Z [Array formula]

Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]

Extract a unique distinct list sorted from A-Z from range

The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]

Exact word in string

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

Extract a list of alphabetically sorted duplicates from a column

The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]

Highlight duplicate values in a cell range

The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]

Highlight unique values and unique distinct values in a cell range

The following formula highlights cells that contain unique distinct values, in other words, all duplicate values except the first instance […]

Extract duplicates from a range

The following array formula in cell B11 extracts duplicates from cell range B3:E8, only one instance of each duplicate is […]

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

Search for a cell value in a dataset

Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]

Merge two columns

  The picture above shows how to merge two columns into one list using a formula. If you are looking […]

How to create a unique distinct list based on two conditions

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

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

Extract a list of duplicates from two columns combined

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]

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

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Identify missing numbers in two columns based on a numerical range

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

Identify missing numbers in a column

The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

Count unique distinct values

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Sort dates within a date range

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

Split values equally into groups

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

Populate drop down list with unique distinct values sorted from A to Z

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

How to highlight duplicate values

Table of Contents How to highlight duplicate values Highlight the smallest duplicate number 1. How to highlight duplicate values The […]

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

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

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

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

Extract a unique distinct list sorted from A to Z

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Count dates inside a date range

How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]

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

Sort a column alphabetically

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Extract all rows that contain a value between this and that

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Delete blanks and errors in a list

Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Calculate machine utilization

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

Extract records between two dates

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]

How to calculate a date based on specific weekday in a month

Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]

How to extract not shared values in two columns

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]

How to identify two consecutive dates in a list

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter […]

Extract shared values between two columns

Table of Contents Extract shared values between two columns Extract shared values between two columns - Excel 365 Extract shared […]

How to extract email addresses from an Excel sheet

  Question: How to extract email addresses from this sheet? (See pic below) Answer: It depends on how the emails are […]

How to search for a string in a column

Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search […]

Filter unique distinct records

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

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Find closest value

Table of Contents Find closest value How to enter an array formula Find closest value - Excel 365 Find closest […]

AVERAGE ignore 0 (zero)

The array formula in cell D3 calculates an average and ignores 0 (zeros). =AVERAGE(IF(B3:B8<>0,B3:B8,"")) The formula above is an array […]

Remove blank cells

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

Find the smallest value in a list that is larger than a number

Problem: Find the smallest value in a list but it has to be bigger than 45? Answer: MINIFS function [Excel […]