How To Number Unique Values In Excel. Discover how today! Oct 17, 2025 · The UNIQUE function is available
Discover how today! Oct 17, 2025 · The UNIQUE function is available to those using Excel for Microsoft 365, Excel for the web, the Excel mobile and tablet apps, and one-off versions of Excel released in 2021 or later. Subtotal: want to view the total number of unique values By mipo in forum Excel Formulas & Functions Replies: 7 Last Post: 01-03-2018, 12:19 PM Nov 30, 2025 · One common task Excel users face is extracting **unique values from a dataset stored in one sheet and displaying them in another sheet**—for example, pulling unique customer names from a sales log in `Sheet1` to a summary dashboard in `Sheet2`. In Excel 365/2021, use UNIQUE and COUNTA to count unique values. Excel doesn't have a simple way to count unique values. Jan 12, 2026 · With COUNTIF and the right Excel formulas, learn how to count unique values in Excel with our easy, step-by-step guide. Sep 23, 2025 · The internal rate of return (IRR) is a metric used in capital budgeting to estimate the return of potential investments. Nov 3, 2020 · for example- in the picture here, the pivot table count is 21 (the amount of rows in the data), but the number of unique values is 18, which is the number I am trying to get. Aug 26, 2022 · To extract a list of unique values from a set of data, ignoring blank cells, you can use the UNIQUE function together with the FILTER function. Or try an existing Random Number Generator for Excel without repeats. Whether dealing with text, numbers, dates, or times, the UNIQUE function is versatile and helpful. In Dynamic Excel, you can use a simpler and faster formula based on UNIQUE. To highlight unique or duplicate values, from the Home tab, use the Conditional Formatting command in the Style Jan 10, 2026 · So, I've switched to the UNIQUE function, which turns a static cleanup chore into a live-updating system that never deletes my source data. Enter the range. Jan 6, 2025 · To count unique values in a set of data, you can use the UNIQUE function together with the COUNTA function. Now add the COUNTIF function. Usually, people remove duplicates and then count the remaining distinct values, which takes a lot of time and effort. If you have Excel 365 or Excel 2021, use the magic UNIQUE function. Counting Unique Case-Sensitive Values Using COUNTIFS in Excel. Dec 24, 2025 · Do you want to count the number of unique values in your Excel data? Knowing the number of unique items in a column when performing data analysis in Excel can provide you with an invaluable overview of your data. Example: How to Sum Unique Values in Excel Summing unique values in Excel is a common task that can be done with a few simple steps. Nov 28, 2022 · How can I match this unique number to every group of duplicates? For example, for every "Belgium" value in the Country find non empty cell and match with all "Belgium" values (in the Unified Number column). This is by far the simplest and most elegant way to do this in Aug 30, 2024 · 1. The values may be numbers, such as real numbers or integers, for example representing a person's height in centimeters, but may also be nominal data (i. This makes FILTER a flexible way to isolate and inspect data without altering the original dataset. Dec 24, 2025 · 8 Ways to Count Unique Values in Microsoft Excel Do you want to count the number of unique values in your Excel data? Knowing the number of unique items in a column when performing data analysis in Excel can provide you with an invaluable overview of your data. is one of the options for the condition, and can check for both duplicate and unique values. To find unique values in Excel, use the Advanced Filter to extract unique values or filter for unique values. Mar 14, 2023 · See how to quickly count unique values in Excel with dynamic array functions. This guide will show you how to sum unique values in a range, column, or row using formulas and examples. The following step-by-step example shows how to do so. Jul 30, 2024 · Discover the simple steps to count unique values in Excel, from using formulas to leveraging built-in tools, ensuring accurate data analysis every time. Download all example in one Excel file. In our first method, we will count the number of unique text values from the contact addresses using the COUNTIFS function. Find unique values from multiple columns in Excel in just few seconds. Mar 23, 2022 · Or use formula =UNIQUE(G2:G5,FALSE,FALSE) to get the unique IDs that have "reject" value for its each entry. Take a look at the numbers in the table above; the unique values are not duplicated. Learn 5 methods to count unique values in Excel using formulas. Excel doesn’t have any single formula that can directly help us to do this. The INDEX formula below uses the full range of data in B3:D11, with a row number of 4 and column Apr 27, 2023 · See how to compare 2 columns in Excel and how to compare and match two lists with a different number of columns. g. Jan 9, 2018 · Here are 5 methods to count the number of unique values. Get your copy of the example Apr 11, 2023 · Learn how to use RAND, RANDBETWEEN and RANDARRAY functions to generate random numbers with no duplicates. Download Excel file to follow along. the date columns have different dates with duplicates date and the values are different with duplicates values. Thank you in advance for your assistance Use the =UNIQUE () function to show all the unique items in the column. Enter the IF and SUM function to count unique values. Apr 15, 2022 · When you want a list of customers, email addresses, product IDs, or something similar where each one is distinct, Excel has a function to help. Mar 12, 2023 · In this article we will see, 5 different ways of counting unique values in Excel. Aug 22, 2024 · Create a dynamic list of unique values in Excel with this guide. Hard coding this isn't an option given that the data needs to be dynamic. Jun 23, 2024 · How to find unique values from multiple columns in Excel. dummies transforms the hard-to-understand into easy-to-use to enable learners at every level to fuel their pursuit of professional and personal advancement. The standard superseded FIPS PUB 5-1 on May 28, 1987, and was superseded on September 2, 2008, by ANSI standard INCITS 38:2009. Duplicate Values. , not consisting of numerical values), for example representing a person's ethnicity. 2. Both methods we'll explain use a combination of functions and variations of the COUNT function. Jan 3, 2023 · Here we will have two parts. To address this, you can utilize the Excel UNIQUE function, which returns a list of unique values from a specified range or list. In this article, we'll look at a step-by-step procedure for counting the number of distinct values in an Excel column that has been filtered. Jun 18, 2024 · Using COUNTIF function with array in Excel is achieved for multiple criteria, unique values and with OR criteria. Mar 31, 2022 · You can use the COUNT function and variations to count cells that contain numbers. Copy the column you care about into another worksheet, then select the entire column. Question: What is the Sum of Unique Values in column B? =SUMPRODUCT (1/COUNTIF (B2:B10, B2:B10), B2:B10) Dec 24, 2025 · Do you want to count distinct values in a list in Excel? When performing any data analysis in Excel you will often want to know the number of distinct items in a column. This is a much simpler formula, but it can run slowly on large data sets. UNIQUE is in a class of functions called dynamic arrays. Federal Information Processing Standard Publication ("FIPS PUB") 5-2 to identify U. states and certain other associated areas. Manually deleting duplicates is error-prone and unsustainable for large or frequently updated data. This tutorial explains how to count unique values based on multiple columns (or conditions) in Excel. e. Learn how to sum unique values in Excel with this step-by-step guide. Simple steps to handle large datasets effectively. This is an array formula, so you need to enter it as an array, and while entering it use Ctrl + Shift + Enter. Video: How to look things up with INDEX What if you want to get the diameter of Mars with INDEX? In that case, we can supply both a row number and a column number, and provide a larger range. Includes formulas and examples for summing unique values in a range, column, or row. It's always so complicated When you download data from a credit card company or bank, they frequently put positive and negative numbers in the same column. Using COUNTIFS for Counting Unique Text Values in Excel. Here, we explain top 2 easy methods along with step by step examples. Validation Quickstart Over the ensuing years, a growing number of market authorities and regulators came to recognize the value of the CUSIP system and embrace its usage. 3. See how to sum values with several AND / OR conditions in Excel 365, 2021, 2019, 2016, 2013, and lower. Sep 3, 2013 · Here’s another quickie way to get the unique value count, as well as to get the unique values. Highlight Cell Rules - Duplicate and Unique Values Highlight Cell Rules is a premade type of conditional formatting in Excel used to change the appearance of cells in a range based on your specified conditions. Learn three easy ways to get unique values from a column in Excel: Remove Duplicates tool, Advanced Filter, and UNIQUE function. Learn how to use Excel count unique values with COUNTIF, SUMPRODUCT, and UNIQUE functions for accurate data analysis. You can already use functions in Excel to total the number of distinct values. Jun 16, 2024 · Learn in Excel to get unique values from range using Advanced Filter, INDEX & MATCH, LOOKUP & COUNTIF, UNIQUE, VBA, Remove Duplicates. Aug 17, 2022 · The Excel UNIQUE function returns a list of unique values in a list or range. But what if you want to count only the distinct values in a cell range? We'll show you two ways to count unique values in Excel. Dec 29, 2006 · Forum Microsoft Office Application Help - Excel Help forum Excel General Duplicate values, adding value to make them unique Oct 1, 2023 · Sum of numbers appearing with unique values Ask Question Asked 2 years, 2 months ago Modified 2 years, 2 months ago Jun 4, 2025 · The Excel FILTER function is used to extract matching values from data based on one or more conditions. Oct 4, 2024 · Learn how to count unique values in a column using formulas like COUNTIF, SUMPRODUCT & the UNIQUE function. Mar 14, 2025 · INDEX returns the value in the 4th row of the range. They don't appear more than once. Question: What is the Sum of Unique Values in Column B? In this example, some values in column B are duplicates and appear more than once, e. Jan 15, 2024 · This tutorial explains how to count the number of unique dates in Excel, including several examples. As only a limited list of symbols can be memorized, a numeral system is used to represent any number in an organized way. Jul 25, 2020 · Here’s what that formula looks like: =COUNTA (UNIQUE (D:D)) This formula tells me that there are 167 unique values in column D. You may also be interested in How to Generate Random Dates in Excel Example In this article, we will be using the following list as an example. Therefore, to apply a case-sensitive match, we have to be a bit trickier. Jan 24, 2023 · This post shows how to use and master the UNIQUE function in Excel. Aug 5, 2025 · How to count unique items (count distinct) in Excel pivot table. Download our practice workbook for free, and exercise! Mar 21, 2023 · The tutorial demonstrates the most efficient ways to find, filter, select and highlight distinct and unique values in Excel. Video shows 3 easy steps, for Excel 2013 or later. This statistic can give you a useful overview of the data and help you spot errors or inconsistencies. Inserting COUNTIFS to Count Unique Values with Multiple Criteria. Aug 30, 2024 · 1. We'll cover each of these validation rules in detail so that you are familiar with all of Laravel's validation features. Formulas to count unique entries in a column, with multiple criteria, ignoring blanks, and more. How to count the number of unique values in a range that contains duplicate values in Excel. [1] The codes are used in Geographic Names Information System Laravel includes a wide variety of convenient validation rules that you may apply to data, even providing the ability to validate if values are unique in a given database table. Use this formula tip when you want to generate sequential random numbers Aug 2, 2022 · To extract a list of unique values from a set of data, while applying one or more logical criteria, you can use the UNIQUE function together with the FILTER function. Jul 2, 2014 · Duplicates aren't bad, but you may need to know how many times a value is repeated or how many unique values are in a data range. The COUNTIF and COUNTIFS functions return case-insensitive matches. In this method, you just have to refer to the list of the values and the formula will return the number of unique values. Mar 21, 2023 · See how to count unique values in Excel with a formula and get an automatic count of all different values in a pivot table. Then do a quick COUNTIF to count the number of times that item shows up. Jul 23, 2025 · This article provides a comprehensive guide on how to count distinct Excel pivot table values, use formulas to count unique Excel entries and learn Excel count distinct value features for better data organization. This post will show you all the ways you can count the number of distinct items in your list. May 13, 2024 · Note: Another way to count unique values is to use the COUNTIF function. Formula examples to extract unique values from a range, based on multiple criteria, sort the results alphabetically, and more. Is there any way to sum just one v Apr 19, 2021 · These ways allow you to deal with almost all kinds of situation when it comes to getting unique values in Excel. To remove duplicate values, select Data > Data Tools > Remove Duplicates. Coda sees this as one value of “dog, cat, fish”. Feb 28, 2022 · This counts the number of unique values, despite using the SUBTOTAL function_num 9 (sum) instead of 3 (count). Mar 31, 2022 · How to Use the COUNT Function in Microsoft Excel Use the COUNTA and UNIQUE Functions This first method is for those who use Excel for Microsoft 365, Excel for the web, Excel 2021, or Excel for iPhone, iPad, or Android phones or tablets. The UNIQUE function is available to those using standalone versions of Excel released in 2021 or later, Excel for Microsoft 365, Excel for the web, and the most up-to-date Excel mobile and tablet apps. We'll show you how to use this function to list unique values and text. , 300. Mar 22, 2023 · Excel SUMIFS function and SUMIF with multiple criteria explained with formula examples. For example: I have the text “dog, cat, fish” in a cell. Nov 26, 2014 · Quick Excel tip to generate unique random numbers in excel using the RAND formula. With sustained reinvestment from the operators of the CUSIP system, CUSIP coverage has steadily grown over the years. Values can be text, numbers, dates, times, etc. Guide to counting the unique and distinct values in Excel. We need to use a different strategy in order to precisely count the unique values in a filtered column. Jul 6, 2024 · Need to learn how to use the COUNTIFS function to count unique values in Excel? We'll show 4 easy methods with proper illustrations to do it. Individual numbers can be represented in language with number words or by dedicated symbols called numerals; for example, "five" is a number word and "5" is the corresponding numeral. Jan 24, 2022 · You can use the =UNIQUE () and =COUNTIF () functions to count the number of occurrences of different values in a column in Excel. Discover how today! Learn about the UNIQUE function, which returns a list of unique values in a list or range. Then use formula =COUNTIFS(A$2:A$14,D2,B$2:B$14,"accept") to get the number of entries accepted (based on final column). Read more. Apr 12, 2023 · If you want to learn how to count only unique values excluding duplicates in Excel, this video will show you a simple and effective formula that you can use Jul 10, 2025 · Learn to sum unique values in Excel by applying specific conditions. Counting Unique Numerical Values with COUNTIFS Function. Select a cell. In the example shown, the formula in D5 is: =UNIQUE(FILTER(B5:B16,(C5:C16="b")*(D5:D16>5))) which returns the 3 unique colors in group B with a quantity > 5. If Col H has the figures below, the formula should add like this 4320+96021+537131+640000, instead of telling me there's 4 unique values. This article covers how to exclude duplicates based on set criteria and summarize data. The UNIQUE function in Excel can either count the number of distinct values in an array, or it can count the number of values appearing exactly once. 4320 4320 4320 96021 96021 96021 537131 537131 537131 640000 Could someone help? Thanks!! Aug 18, 2020 · However, there is always a way out! This tutorial will serve as a guide on how to count unique and distinct values without duplicating them. In the example shown, the formula in D5 is: =UNIQUE(FILTER(B5:B16,B5:B16"")) which outputs the 5 unique values seen in D5:D9. I like to have the numbers separated into two columns. If source data or criteria change, FILTER will return a new set of results. When I try to convert above text into a list - either using List() or ListCombine() - Coda returns [(dog, cat, fish)] instead of [(dog),(cat),(fish The quickest and easiest way to count the number of unique values in an Excel range is by using the UNIQUE function, available in Microsoft Excel 365, Excel for the Web, and Excel 2021. Jan 5, 2023 · I have two columns one for date and one for value. I don’t even need to first run the UNIQUE function on its own, I can do this all in one cell and quickly get the number of unique values in the list. Assigning a Serial Number to Duplicate Values in Excel Here we will first use the COUNTIF formula and then fill in all the results to get our final output. In the example shown, the formula in F6 is: =SUMPRODUCT (1/COUNTIF (B5:B14,B5:B14)) In Dynamic Excel, you can use a simpler and faster formula based on UNIQUE. UNIQUE is a power dynamic array function which you need to know. FIPS state codes were numeric and two-letter alphabetic codes defined in U. If a number appears more than once, you count them once. Oct 30, 2013 · I'm looking for a way that I can tell Excel to look through G:G (the total number of rows needs to be dynamic) and return the number of unique days, which in this case is 5, so that I can use this value later on in some other code. S. With and without multiple criteria. This example shows you how to create an array formula that counts unique values in Excel. This is because the UNIQUE function is only available in these and later versions of Excel. Here is the formula for calculating it. Column D is the end result, which I did manually. Apr 12, 2023 · See how to get unique values in Excel with the UNIQUE function and dynamic arrays. Learn formulas for counting unique names, texts, numbers, case-sensitive unique values, and more. Sep 3, 2024 · Assign unique numbers to duplicate values in Excel to differentiate repeated entries, making it easier to identify and manage repeated data. Learn step-by-step methods to extract and update unique data automatically as entries change. Feb 4, 2019 · I’m trying to take a simple comma-separated list and convert it into unique values so that I can parse them in other tables. 4. Anything greater than 1 means that there is a duplicate. In the example shown, the formula in F5 is: =COUNTA(UNIQUE(B5:B16)) which returns 7, since there are seven unique colors in B5:B16. The output from FILTER is dynamic. Jan 5, 2021 · I am trying to assign a unique serial number to column C if column A is the same therapeutic class and column b is a different drug so I am looking for a formula that can help me with this task. Apr 10, 2025 · Learn how to extract unique values in Excel based on specific criteria using array formulas or the Kutools for Excel feature with step-by-step guidance. Jul 29, 2024 · In this instructive session, I’ll demonstrate 5 handy methods to count unique values in a filtered column in Excel with proper explanations. The first part is about assigning serial numbers to unique values, and the next part is about assigning serial numbers to duplicate values. Find out how to exclude empty cells and improve performance for large datasets. Here's how to tackle these tasks in Excel. Jul 28, 2024 · Dataset Overview In large datasets, duplicate values or repeated occurrences of the same values can be a common issue. So, if you want to count unique values in a Pivot Table, use helper columns and if you want to count distinct values, you can use the inbuilt functionality (in Excel 2013 and above) or can use a helper column. Aug 21, 2022 · To count the number of unique values in a range of cells, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In Excel, there are several ways to filter for unique values—or remove duplicate values: To filter for unique values, select Data > Sort & Filter > Advanced. We can use multiple criteria to count the unique values. Whereas, distinct values are the different numbers in the collection. How to use the SUMIFS function in Excel, one of Excel’s math and trig functions, to add arguments that meet multiple criteria. . Aug 4, 2024 · 4 Different Examples to Count Unique Values with Criteria by COUNTIFS in Excel. Let’s see the dataset first. We can also count the number of unique numerical values from the contact addresses using the COUNTIFS function. Dec 9, 2020 · The easiest way to spot a unique value is to format it, and Microsoft Excel offers two ways to do so.