'COUNTIF function' category
This article demonstrates a formula that creates a frequency distribution table from a multi-column cell range which is useful in […]
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the […]
Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. […]
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]
This article demonstrates two formulas that calculate averages, the first formula calculates an average based on criteria, and the second […]
This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 […]
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 […]
This article demonstrates ways to sum values based on criteria. Table of Contents SUMPRODUCT - based on a list of […]
This article demonstrates formulas that check if a cell value is equal to any value in a given list. Table […]
To extract groups from cell range B3:B10 I use the following regular formula in cell B13. =LOOKUP(2,1/(COUNTIF($B$12:B12,$B$3:$B$10)=0),$B$3:$B$10) Copy cell B13 […]
This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]
The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]
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 […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
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 a few different formulas that extract values from the table column header names and the right-most column. […]
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 sorts values arranged in a column from A to z by every other value. […]
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]
This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
How would you figure out an unique list where the sum of in one column doesn't match the other column? […]
This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
This article demonstrates formulas that display dates that follow each other in sequence. Dates June 5, 2025 and June 6, […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Counts the number of cells that meet a specific condition.
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]
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 […]
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]
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 […]
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 […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
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.
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Highlight […]
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
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 […]
Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]
Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]
Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
This blog article describes how to create teams randomly. There are twenty names in column B and four teams in […]
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]
Table of Contents Vlookup with multiple matches returns a different value Lookup with multiple matches returns different values - Excel […]
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
The image above demonstrates an array formula in cell C25 that extracts numbers based on how far off they are […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
This article demonstrates formulas that list unique distinct values if they contain a specified substring. Table of contents Extract unique […]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
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 […]
Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]
I will in this article show you how to extract the most frequent value (text or number) between two dates […]
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 […]
Lookup with criteria and return records.
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]
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 (single column) Unique distinct values sorted based on frequency […]
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 […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
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 […]
Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]
Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any […]
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, […]
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 B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
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, […]
The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]
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 […]
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 […]
Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]
Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]
Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]
The formula in cell E14 adds a number from column C if the corresponding value in column B is unique […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
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: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
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 […]
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 […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
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 […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]
Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]
Table of Contents How to rank text uniquely without duplicates How to rank uniquely based on a condition Get Excel […]
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]
Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
The regular formula in cell E6 extracts unique distinct values from column B based on the corresponding number in column […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]
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 […]
This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]
This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]
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 […]