Count Unique Values Excluding Duplicates by Formula in Excel Step 1: In E2 which is saved the total product type number, enter the formula “ = SUMPRODUCT (1/ COUNTIF (B1:B11,B1:B11)) ”. To count only unique values we have to combine several Excel functions. by Svetlana Cheusheva | updated on October 9, 2020 To handle possible errors, wrap the IFERROR function around your formula and instruct it to output 0 if any error occurs: As the result, you get a count based on the database concept of unique: Now that you know how to count unique cells in a column, any idea on how to find the number of unique rows? CREAM PROMO 99 AL DHAFRA SM CREAM BISC 11205001 269 30 If you have Excel 365, you can use the UNIQUE function to count unique values with a much simpler formula. We'll look into your task and try to help. Instead of building formulas or performing intricate multi-step operations, start the add-in and have any text manipulation accomplished with a mouse click. While i understand the array distinct formula, i am not able to understand the regular distinct formula. Corp WC WC1 Fran EC 1 So it's suppose to count the unique 'Site' values in the Main table (becuase there are many duplicates) if the 'Region' and 'Format' matches that in the current table. Count unique values in pivot table with Value Field Settings in Excel 2013 and later versions. Is there a way to list (and keep updated) unique values from multiple worksheets? I have duplicate Numeric Data in Column A, against the cell B with a different unique Names and want to unique count in column C with every unique Name. Clicking the ‘Module’ will open a new module in the Excel. Please include the link to your blog comment. Copyright © 2003 - 2021 4Bits Ltd. All rights reserved. Read these articles for more details on CountIF Function or SumProduct Function. Let’s see an example to make things clear. I need help with count distinct in the below table- 9 25 VLOOKUP in Excel - which formula is the fastest? =IFERROR(ROWS(UNIQUE(FILTER(Table_SDCdata[Site], (Table_SDCdata[Format]=[@Format]) * (Table_SDCdata[Region]=[@Region])))), 0). However, Excel does not work correctly with so much data. First, we need to check if each value is a duplicate, then we need to count the remaining entries. If the Jan and Feb columns are the numbers of buyers, then you can use the SUMIFS function to find the sum for those columns with certain conditions. I can get the total unique values no problem and if I split down the Salesman that is fine also but I would like one formula rather than splitting the data every time i need to do this report! The simplest and easiest way to count distinct values in excel is to use SumProduct and CountIF formula. 5 5 7 8 essential tools to streamline your email workflow. When counting unique values, use the following expression: =SUMPRODUCT((range<>"")/COUNTIF(range,range&"")) Figure F shows this function at work in our example data... sort of. To remove duplicate values, click Data > Data Tools > Remove Duplicates . The resulting array contains a set of numbers combined with False for the blank cells. After that, you simply enclose the formula in the ROWS function to calculate the number of rows. Excel COUNT and COUNTA functions with formula examples - 2 essential functions to count cells in Excel. It returns 0, as you did. Hi , Dear experts, Can you please help me, I am not the best at Excel but I try! This comprehensive set of time-saving tools covers over 300 use cases to help you accomplish any task impeccably without errors or delays. Unable to open Outlook window" error, Outlook Quick Parts and AutoText: how to create, edit and use, Merge data from duplicate rows based on a unique column, How to compare data in two Google sheets or columns. This article describes how to count unique distinct values. Here, ‘data’ represents the range that contains the values. Count unique values using Sum and Countif function. Count unique values in a column in Excel Find all distinct values in a column using the Advanced Filter. This will help us provide a quick and relevant solution to your query. What are unique distinct values? I would like to list unique values (EG: Client) across all active sheets. 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. Thanks! Count Unique Values with an Excel Formula. To count the unique values (don't be overwhelmed), we add the SUM function, 1/, and replace 5 with … The easiest way to count unique values in a column is to use the UNIQUE function together with the COUNTA function: The formula works with this simple logic: UNIQUE returns an array of unique entries, and COUNTA counts all the elements of the array. What are unique distinct values? The best spent money on software I've ever spent! The integration of power pivot with excel (known as Data Model), has provided some powerful features to the users. Below is the code to write your own UDF that does the same: To embed this function in Excel use the following steps: To use the UDF, you can simply type the UDF name ‘CountUnique’ like a normal excel function. Chocolate 40gm Next, the results of CountIF formula are used as a divisor with 1 as numerator. an excel formula to count unique text values in a range. Method – 1: Using SumProduct and CountIF Formula: Method – 2: Using SUM, FREQUENCY AND MATCH Array Formula. Count unique numeric values in a range. You can use Advanced filter to get the count of unique values in a given range of cells, just do the following steps: Step1: select range that you want to count unique values, such as: B1:B6 Step2: go to DATA tab, click Advanced command under Sort & Filter group. Excel Tutorials : 100 Excel Tutorials. Countif function counts all the product names as well . If you replace your formula with, =IFERROR(ROWS(UNIQUE(FILTER($B$2:$B$1000, ($A$2:$A$1000=A2) * ($D$2:$D$1000=D2)))), 0). Using such a formula may render incorrect results, which aren't very obvious for users, without thorough checking. I need to count the no of customers who purchased cream under each month , irrespective of the flavor. Using RATE function in Excel to calculate interest rate, Attaching files from SharePoint to Outlook email, How to attach files to Outlook email from OneDrive, LARGE IF formula in Excel: get n-th highest value with criteria, Compare 2 columns in Excel for matches and differences, CONCATENATE in Excel: combine text strings, cells and columns, Create calendar in Excel (drop-down and printable), 3 ways to remove spaces between words in Excel cells, How to fix "Cannot start Microsoft Outlook. Later you can count a new list of distinct values using ROWS or COUNTA function. Incredible product, even better tech support…AbleBits totally delivers! Popular Articles: The VLOOKUP Function in Excel. Generic formula to count unique text values in excel = SUM (--( FREQUENCY ( MATCH (range,range,0), ROW (first_cell_in_range)+1)>0)) Range : The range from which you want to get unique values. Everything else in my formula works. Corp EC EC1 Corp EC 2 (This would be EC1 and EC2) If you have Excel 365, you can use the UNIQUE function to count unique values with a much simpler formula. Unfortunately cannot use the COUNTBLANK formula to add to the existing formula and subtract records when containing a string to come to the correct result. That's how to count unique values in Excel with the new dynamic array functions. Spread the Word! E5 35+ handy options to make your text cells perfect. Now let us discuss each of the methods in detail along with an example – #1 Count Unique Values using SUM and COUNTIF Function The trick is to "feed" the entire range to UNIQUE so that it finds the unique combinations of values in multiple columns. I'm sorry, it is not very clear what result you want to get. Count Unique Values that are Text; Count Unique Values that are Number; Using Advanced Filter; Steps to Count Unique Values in Excel 2016: Count Unique Values Using Formula. Hello, I'm trying to count unique individuals whose work location(s) are Ashburn (Column E) which could be depicted as 123 main st., Ashburn VA. An individual (Column A) can work at multiple Ashburn locations but I am only interested in counting them once. Let me start by noting that Power Query is available only to those who are running Excel 2010 or later for Windows. Anyone who works with Excel is sure to find their work made easier. Count Only Unique Text Values from a List. In the meantime, you can use these formulas: Thanks for a terrific product that is worth every single cent! And my apologies for not testing the formula when the criteria are not met. But this result needs to fix the corresponding raw of the A Column. Any reason as to why it's not working? There are a plethora of tutorials on how to identify and count unique values on the Excel work file. One of the easiest ways of counting unique values is the use of advanced filters. Your formulas an explanantions are very helpful thanks! Hello! CREAM CREAM 4900 AL MADINA HYPERMARKET ORANGE CREAM 11105028 21, CREAM CREAM No of customers who purchased Variant Cream 4 3 =IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C1016))), 0), You just need to add each condition separately, like this: error message as expected, but F5 displays a value of 1 as COUNTA also calculates the cell which contains a string. Will it also be because of the data size? CREAM CREAM 6079 AL AHLI S/M MANGO CREAM 11105029 21 As usual, your tutorial gets directly to the point in a friendly manner. Related Posts. Later you can count a new list of distinct values using ROWS or COUNTA function. Count the number of unique values by using excel functions To get the unique count of values using the advanced filter option In this method user can use the “Advanced Filter” option to get the unique values from the data, paste them into a new location and then use the excel “count… Example: Count Unique Text Values Excel In an excel sheet, I have this data of names in range A2:A10. But, before starting let’s make our goal clear. This is a good option if you … Duplicate Remover for Excel - all-in-one tool to find, highlight, delete, copy and move duplicate and unique values. However, I tested this formula, it works. My only aim is to turn you guys into 'Excel Geeks'. Hello! Thank you very much for catching this error! Count Values for a Given Unique Text Value in Excel – Example #2 In the below-mentioned example, Table contains a date in the column G (G5 to G20) & day in the column (H5 to H20). Example MATCH(0, INDEX(COUNTIF($B$1:B3, $A$2:$A$10), 0, 0), 0)) Thank you! Excel Pivot Table Count Unique. 1. They are all values but duplicates are merged into one distinct value. It gives me 0 as result? In the above example, count the unique values , if the other column contain "ball" ( basketball /Volleyball ), =IFERROR(ROWS(UNIQUE(FILTER(A2:A10,ISNUMBER(FIND("ball",B2:B10,1))))), 0), Hi, CREAM CREAM 7935 AL DOURIZ MANGO CREAM 11105029 22 4 I have enjoyed every bit of it and time am using it. By looking at the above image, we can clearly see that what we mean by counting unique value in excel. Here, In the dataset there is a repetition of days, Here I need to get the count of unique day i.e. A pivot table is also an excellent way to count unique values. Adding up all the divisions (column E) returns the count of unique values in the range (A2:A14), which is 5. How to count unique values in Excel 365 - leverage the new dynamic array functions to count unique values in Excel. To count unique distinct records, read this article: Count unique distinct records. Start with SUMPRODUCT function =SUMPRODUCT(; Type “1/” to divide 1 by next … We also need to use an array function. MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)) An alternative option for counting unique values by 2 conditions is, =SUMPRODUCT((($A$2:$A$10000=A2) * ($D$2:$D$10000=D2)) / COUNTIFS($A$2:$A$10000, $A$2:$A$10000, $D$2:$D$10000, $D$2:$D$10000, $B$2:$B$10000, $B$2:$B$10000)). Dark Chocolate 20gm*24 box First, we need to check if each value is a duplicate, then we need to count the remaining entries. We cannot guarantee that we will answer every question, but we'll do our best :), 60+ professional tools for Microsoft Excel. =IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10>16) * (C2:C10<19)))), 0), some commonly use function in excel explan with example. I've used wildcarding before but I can't get this correct. Any other workaround? Paste the UDF code in the module window as shown below. Keep up the great work, and many thanks for the professional work you spread around with your tutorials! In my sheet, col A is customer name, col B is product name, col C is job activity. I saw an article on getting distinct values. I hope you have studied the recommendations in the above tutorial? A small example (in case anyone else reads the post for future): Count the number of unique values by using excel functions To get the unique count of values using the advanced filter option In this method user can use the “Advanced Filter” option to get the unique values from the data, paste them into a new location and then use the excel “count… Microsoft and the Office logos are trademarks or registered trademarks of Microsoft Corporation. A couple of years ago, we discussed various ways to count unique and distinct values in Excel. White Chocolate 15gm Biscuits W/Marshmallow300gm “Tuesday” only from this dataset range (H5:H20). So, the formula in F5 would go as follows: We have updated this and a few other formulas in this tutorial. Count unique values formula examples (.xlsx file). To count unique values with multiple criteria we can use boolean logic: Count Unique Value With Multiple Criterias With And Logic {=SUM( - - ( FREQUENCY ( IF (condition1 * Condition2, MATCH (range,range,0)), ROW (range)- ROW (firstCell in range)+1)>0))} We will do our best to keep up and (hopefully!) If you set this parameter as true then it will count the blank rows as unique. Because, CountIF function generates a ‘0’ for each blank cell and when 1 is divided by 0 it returns a divide by zero error. Removing the duplicates from view. To fix this, you can use the following variant of the above formula that ignores the blank cells: The formula that we discussed above is good to be used for small ranges. 11 11 33 The main focus is on how to count unique values in a column using different ways in excel. The integer part of this number represents days since the fixed moment for all dates. To count only unique values we have to combine several Excel functions. This is achieved in just three steps – identification, filtrating, and copying of the unique values. Thank you for pointing out this issue! Finally, the SUM function computes the sum of all the values and returns the result. This is the formula I'm using, and it's giving me a reulst of 0 for all... There are many duplicates in the Masterfile for each site that matches the region and format, therefore I only want to count unique once. I am trying to use wild cards to capture multiple Ashburn locations, Ashburn 1, Ashburn 2, etc. Google Chrome is a trademark of Google LLC. Though this can be a very common requirement in many scenarios but excel doesn’t have any single formula that can directly help us to count unique values in excel inside a range. This means that, if a value appears 4 times in the range then its value will become as 1/4 = 0.25 and value that is present only once will become 1/1 = 1. This modifies the result of CountIF formula such that; values that only appear once in the array become 1 and the numbers corresponding to duplicate values become fractions corresponding to the multiple. Just for the data size example: Main table I'm counting and filtering has 37 columns and 140 500 rows. This smart package will ease many routine operations and solve complex tedious tasks in your spreadsheets. After installing Kutools for Excel, please do with the following steps:. Range : The range from which you want to get unique values.. firstCell in range: It is the reference of the first cell in range.If range is A2:A10 then it is A2. AbleBits suite has really helped me when I was in a crunch! Do let us know your own methods or tricks to do the same. which uses the row number of each item in the data and the row number of the first item in the data to build a straight, sequential array like this: What I'm trying to achieve is the following: Count all the unique 'Site' codes in the Master table, if the 'Region' and 'Format' matches that from the template table. In such cases the formula throws a ‘divide by zero’ error. As above we have discussed that you can build your own formula to count the unique values in a range of cells. Other ways to count unique values. This article describes how to count unique distinct values. I recommend reading this section above on calculating unique values by condition. Unfortunately, without seeing your data it hard to give you advice. Any ideas? About Author: Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. So, these were all the methods that can help you to count unique value in excel. 1 2 1 1 I am happy to hear you had a positive experience with our tutorials. This time, we want to know the number of unique records that occur only once. The CountIF function looks inside the data range (B2:B9) and counts the number of times each value appears in data range. The goal of this site is to make you an excel geek. Following is the generic formula that you can use: = SUMPRODUCT (1 / COUNTIF (data,data)) 7 9 7 11 Let’s first understand the role of inner CountIF function. CREAM CREAM 99 AL DHAFRA SM ORANGE CREAM 11105028 21 Hello, I am hoping you can help me with the below. To count unique values in Excel ignoring blanks, employ the FILTER function to filter out empty cells, and then warp it in the already familiar COUNTA UNIQUE formula: COUNTA(UNIQUE(FILTER( range , range <>""))) In this example, we are going to find out how many different winners there are in a specific sport in F1 (criteria 1) and under the age in F2 (criteria 2). Fran EC EF1 Exp EC 1 Next. Doing this will show the total count including the duplicate values, however we only need to get the count of distinct values. Method 5 – COUNTUNIQUE User Defined Function: VBA Loops – For, For Each, Do While and Do Until Loops, How to Insert a Checkbox in Excel (In 5 Easy Steps), Round Function In Excel – The Complete Guide, VBA Substring – How to Substring in Excel Macro, Counting Unique Values In Excel – 5 Effective Ways, VBA On Error Statement – Handling Errors in Excel Macros, Excel VLOOKUP – Massive Guide with 8 Examples, How to Find External Links or References in Excel. Thank you Alexander, I am having trouble with the criteria data (E1) in your example. improve. The reference evaluates non-contiguous values. White Chocolate 25gm*24 So, in our case the resulting array would be like: This array is fed to the FREQUENCY function which returns how often values occur within the set of data and finally the outer IF function sets each unique value to 1 and duplicate value to FALSE. not sure why there is a index function as it always return a value of 1 instead of an array not matter what is the range of column "B". CREAM CREAM 6079 AL AHLI S/M CHOCOLATE CREAM 11105026 21 5 Thank you very much for your feedback! In this formula, we have used ISTEXT function.