## 'COUNTIF function' category

Get date ranges from a schedule
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
Get the latest revision
Table of Contents Get the latest revision Create a list with most recent data available 1. Get the latest revision […]
AVERAGE based on criteria
This article demonstrates two formulas that calculate averages, the first formula calculates an average based on criteria, and the second […]
If cell contains multiple values
This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 […]
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 […]
SUMPRODUCT – multiple criteria
INDEX MATCH – multiple results
Table of Contents INDEX MATCH - multiple results INDEX and MATCH - multiple criteria and multiple results INDEX and MATCH […]
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 […]
How to use the COUNTIF function to count not blank cells
The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]
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 […]
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 […]
Count unique distinct values based on a condition
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
Extract a list of alphabetically sorted duplicates based on a condition
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
Extract duplicate values with exceptions
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
Filter duplicate values based on criteria
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
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 […]
Rotating unique groups with no repeat
Time sheet for work
I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]
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. […]
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 […]
Dynamic scoreboard
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
How to build a Team Generator – different number of people per team
JD asks in this post:Â Dynamic team generator Hi, what if we have different number of people per team? So in […]
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 automatically add new items to a drop down list
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
Highlight unique values in a filtered Excel table
Table of contents Highlight unique values in a filtered Excel table Highlight unique values and unique distinct values in a […]
Compare two columns and extract differences
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
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 all sequences of consecutive dates
This article demonstrates formulas that display dates that follow each other in sequence. Dates June 5, 2025 and June 6, […]
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 COUNTIF function
Counts the number of cells that meet a specific condition.
How to sort a data set using three different approaches, built-in tools, array formulas, and VBA
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
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 […]
Repeat values across cells
This article explains how to repeat specific values based on a table, the table contains the items to be repeated […]
Group rows based on a condition
What's on this page Group rows based on a condition Merge matching rows 1. Group rows based on a condition […]
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 […]
Compare the performance of your stock portfolio to S&P 500 using Excel
How to use VLOOKUP/XLOOKUP with multiple conditions
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]
Filter values that exists in all three columns
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
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 […]
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 […]
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 […]
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 […]
Extract unique distinct values in a filtered list
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Extract unique distinct values based on a filtered Excel defined Table
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
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 […]
Count unique distinct values that meet multiple criteria
This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an […]
Vlookup a cell range and return multiple values
VLOOKUP a multi-column range and return multiple values.
VLOOKUP – Return multiple unique distinct values
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Compare tables: Filter records occurring only in one table
Table of Contents Compare tables: Filter records occurring only in one table Compare two lists and filter unique values where […]
Compare two columns and return differences
This article demonstrates formulas that extract differences between two given lists. The first formula in cell B11 extracts values from […]
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. […]
Sort rows based on frequency and criteria
Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]
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. […]
Team Generator
Table of Contents Team Generator Dynamic team generator 1. Team Generator This section describes how to create teams randomly. There […]
Vlookup with multiple matches returns a different value
Table of Contents Vlookup with multiple matches returns a different value Lookup with multiple matches returns different values - Excel […]
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Create dependent drop down lists containing unique distinct values
Table of Contents Create dependent drop down lists containing unique distinct values Create dependent drop down lists containing unique distinct […]
Extract a unique distinct list sorted from A to Z ignore blanks
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
Automate net asset value (NAV) calculation on your stock portfolio
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 […]
Create number series
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
Extract unique distinct values if the value contains the given string
Most frequent value between two dates
I will in this article show you how to extract the most frequent value (text or number) between two dates […]
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 sorting 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 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 […]
How to list unique distinct values sorted by frequency
What's on this page Unique distinct values sorted based on frequency (single column) Unique distinct values sorted based on frequency […]
Extract unique distinct values sorted from A to Z
Table of Contents List a unique distinct list from a column sorted A to Z Extract a unique distinct list […]
Extract unique distinct values A to Z from a range and ignore blanks
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
Find min and max unique and duplicate numerical values
Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]
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"... […]
Search and display all cells that contain multiple search strings
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]
Filter values in common between two cell 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
Table of Contents Filter values occurring in range 1 but not in range 2 Filter not shared values out of […]
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 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 […]
Extract duplicates from a multi-column cell range
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]
How to perform a two-dimensional lookup
Table of Contents How to perform a two-dimensional lookup Reverse two-way lookups in a cross reference table [Excel 2016] Reverse […]
Extract a list of duplicates from three columns combined
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
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 unique distinct list from three columns
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
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 […]
Find missing numbers
Table of Contents Find missing numbers in a column based on a given range Find missing three character alpha code […]
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 […]
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 Highlight more than once taken course in […]
Filter unique distinct values based on a date range
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
How to rank text uniquely without duplicates
Table of Contents How to rank text uniquely without duplicates How to rank uniquely based on a condition Get Excel […]
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 […]
Sum unique numbers
Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]
Extract a unique distinct list sorted from A to Z
Table of Contents Extract a unique distinct list sorted from A to Z Unique distinct list sorted alphabetically based on […]
Working with unique values
What's on this page Extract unique values from two columns - Excel 365 Extract unique values from two columns - […]
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 - Excel 365 Sort a column using array formula Two columns sorting by the […]
Extract unique distinct values from a multi-column cell range
This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]
Extract a unique distinct list and ignore blanks
How to extract not shared values in two columns
Question: How do I remove common values between two lists? Answer: I created the formulas demonstrated in this article, they […]
Extract shared values between two columns
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
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?
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
Find closest value
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
Cleaning Up Excel Worksheets: Eliminating Blank Cells, Rows, and Errors
In this blog post I will demonstrate methods on how to find, select, and deleting blank cells and errors. Why […]