'IF function' category
The image above shows the performance across industry groups for different date ranges, conditional formatting makes the table much easier […]
This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]
Column B contains numbers, the formula in cell D3 calculates the smallest value excluding zeros. Note that all numbers are […]
The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]
The picture above shows different values in column B and a formula in column C that tries to identifies the […]
Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]
Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character. =LEFT(B3,SEARCH(" ",B3)-1) […]
This article demonstrates two formulas that calculate averages, the first formula calculates an average based on criteria, and the second […]
This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]
This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]
This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]
The AVERAGE function ignores empty cells, text values, and boolean values automatically, however, it doesn't handle error values. The AVERAGE […]
Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]
This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]
I have demonstrated in a previous post how to simplify nested IF functions, in this article I will show you how […]
You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]
This article demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is […]
This article demonstrates formulas that check if a cell value is equal to any value in a given list. Table […]
This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]
This article shows examples of how to use the IF function with the OR function. Table of Contents IF with […]
This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]
This article demonstrates different formulas based on if a cell contains a given text. Formula in cell C3: =B3=$E$3 The […]
The image above demonstrates a formula that matches a value to multiple conditions, if the condition is met the formula […]
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]
Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]
The following formula performs a two-way lookup in two different tables.
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]
This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates a formula that extracts values from a column based on a date range criteria and another condition. […]
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
Add cell values to a single cell with a condition, no VBA in this article.
This article demonstrates how to extract unique distinct values based on a condition and also 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 […]
This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]
This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]
This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
This article demonstrates a formula What's on this page Question Formula Array formula Explaining array formula Get the Excel File […]
I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]
This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]
This article demonstrates a recursive LAMBDA function and a User Defined Function (UDF) that multiplies numbers in each row with […]
This time I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post […]
In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]
This article describes how to filter records based on the maximum value of a specific item. There are names in […]
(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]
This article demonstrates array formulas that perform a wildcard search based on a sequence of values. The formulas return the […]
This article demonstrates array formulas that identify two search values in a row or in a sequence. The image above […]
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]
This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]
This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
This article demonstrates a workbook that allows children to practice basic mathematics or more specifically arithmetic calculations. The image above […]
The image above shows a formula in cell C2 that searches for a value based on two conditions specified in […]
This article demonstrates formulas that display dates that follow each other in sequence. Dates June 5, 2025 and June 6, […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
The SUBSTITUTE and REPLACE functions can only handle one string, this article demonstrates two ways to handle more than one […]
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]
This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]
This article demonstrates a worksheet that highlights lookups across relational tables. I am using Excel defined Tables, if you add […]
I will in this article demonstrate four formulas that do lookups, extract unique distinct and duplicate values and sums numbers […]
In this post, I am going to show you how to extract unique distinct values and duplicates using a formula, […]
This article demonstrates how to set up a chart so it shows one color for increasing bars/columns and another color […]
Excel 2010 has a PowerPivot feature and DAX formulas that let you work with multiple tables of data. You can […]
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]
I will in this article demonstrate how to search a table for a date based on a condition and then […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]
The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]
This article demonstrates a formula that creates date ranges based on a given number of days and the end date […]
This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Danielle asks: I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes […]
This article demonstrates how to automatically enter data in cells if an adjacent cell is populated using VBA code. In […]
The RAND() function In Excel returns a number greater than or equal to 0 (zero) and less than 1. Combining […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This schedule uses the year and month in cell D1 and K1 to highlight activities like vacation specified in the […]
Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department. I have a […]
This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]
Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]
The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]
This post describes how to search filtered values in an Excel defined Table using a condition given in cell 12 and return […]
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
VLOOKUP a multi-column range and return multiple values.
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
Question: List of data and blank cells in a column which will be added from day to day. There are […]
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
This post describes two ways to extract all matching strings from cells in a given cell range if they contain […]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
Question: I have a question that I can’t seem to find an answer to: I want to make a full […]
This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]
Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
This weekly calendar is easy to customize, you can change calendar settings in sheet "Settings": Start date (preferably a Sunday or […]
I have created another monthly calendar template for you to get. Select a month and year in cells A1 and […]
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]
Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
Table of Contents Vlookup with multiple matches returns a different value Lookup with multiple matches returns different values - Excel […]
Sam asks: One more question for the Calendar that you have set up above can we have a excel formula […]
In this post I am going to add one more function to the weekly schedule I built in a previous […]
The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]
Below is an excel table containing recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]
This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The array formula in cell B15 extracts dates from B4:B12 if it is not a duplicate item in the same […]
This article demonstrates how to extract the largest number smaller than a given number based on a condition and criteria. […]
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
The image above shows a formula in cell C11 that extracts values from column D if the number in cell […]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Table of Contents VLOOKUP […]
VLOOKUP and return multiple matches based on many criteria.
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]
The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
Table of Contents Search values distributed horizontally and return the corresponding value Filter values distributed horizontally - Excel 365 1. […]
I will in this article show you how to extract the most frequent value (text or number) between two dates […]
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 […]
I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]
This post explains how to lookup a value and return multiple values. No array formula required.
Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]
A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]
Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Lookup with criteria and return records.
The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]
What's on this page Unique distinct values sorted based on frequency Unique distinct values sorted based on frequency - Excel […]
Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]
Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]
I this article I will show you how to get numerical values from a cell range manually and using an […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]
Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]
This article demonstrates two different formulas, one for Excel 365 and one for earlier versions. Table of Contents Reverse a […]
Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
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, […]
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
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, […]
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]
Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]
The following formula highlights cells that contain unique distinct values, in other words, all duplicate values except the first instance […]
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]
The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]
Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]
Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
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 […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Table of Contents How to highlight duplicate values Highlight the smallest duplicate number 1. How to highlight duplicate values The […]
This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
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)) + […]
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]
This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
This article presents methods for filtering rows in a dataset based on a start and end date. The image above […]
Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
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 […]
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]
Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search […]
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
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 […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
This article demonstrates formulas that lets you extract the smallest number larger than a given number. The example above specifies […]
The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]