- Do you have an extensive set of data?
- Is it hard to search for some unique and distinct values?
- Do you need to know how many unique or distinct values you have in your data set?
When we talk about a data set, we often mean a long list that contains a wide variety of values. Sometimes showing and counting some particular values helps us to make a more concise and practical data list. For example, assume a customer’s name list. Probably, some of them are mentioned more than one time. So we need a list of the names that are mentioned only once. In other words, we need Unique names.
BTW, Assume you have a list of your customers. You need to count the number of customers, filter them normally or conditionally and get a list of them. But your dataset is too long, and values repeat more than once. Counting Distinct values helps you with this issue.
In this blog, we are going to tell you everything you need to know about counting Unique and Distinct values.
What is Unique and Distinct Value?
Assume that we have this data set:
1,2,4,8,2,7,8,9,1,6
Unique Values:
Here, among these ten values, you can see some of them are mentioned once. Such as 4, 7, 9, and 6. These are Unique values.
Distinct Values:
The value appearing for the first time in a data set, whether unique or repeated further, is a Distinct value.
You can count these data, filter them normally or conditionally and get a list of them.
The easiest way to count and show these values is using the If and Countif functions. So let’s proceed and learn how to use them based on your data set.
Show and Count by If and CountIf Functions
By If and CountIf functions, we determine which values are Unique and which ones are Distinct. You can see the results in figure 3 (here, we represent Unique values.)
How Do I Show Unique Values in Excel?
According to figure 3, Before we can count unique values, we need to detect them in our data set. Follow these steps to do so:
- Add a heading to your data set.
- Insert a column to show the result.
- Insert =IF(COUNTIF(data set range, the first cell of data set)=1, “Unique”, ” ”) syntax.
- Make sure your data set range is fixed by the dollar sign ($) with attention to figure 3.
- Fix the first cell of your data set by dollar sign according to figure 3.
The syntax:
According to our example, the syntax is:
=IF(COUNTIF($A$2:$A$11,$A2)=1, “Unique”, “”)
Be careful with the dollar signs.
How Do I Show Distinct Values in Excel?
We follow the previous steps to specify Distinct values by using If and Countif functions.
The syntax:
According to our example, the syntax is:
=IF(COUNTIF($A$2:$A2,$A2)=1, “Distinct”, “”)
Be careful with the dollar signs.
Show Unique or Distinct Values in a Row
Sometimes, you need to show Unique or Distinct values in a row. For example, your data set may include “company” and “product.” You want to determine the Unique row as shown in figure 5.
The syntax:
According to our example, the syntax is:
=IF(COUNTIFS($A$2:$A$9,$A2,$B$2:$B$9,$B2)=1, “Unique row”, “”)
Be careful with the dollar signs.
While doing every single step in the Show Unique values section,t add the second data column according to figure 5 (yellow box.)
In addition, you must use the CountIfs function instead of the CountIf.
Here to show Distinct values, follow the steps we mentioned before, in the section about Showing distinct values, however use Countifs instead of countif.
The syntax:
According to our example, the syntax is:
=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1, “Distinct row”, “”)
Be careful with the dollar signs.
Figure 6- Show the Distinct row
Sometimes you need to show Distinct values in a row. For example, your data set includes “Company” and “Product.” You want to determine the Distinct row as shown in figure 7.
The syntax:
According to our example, the syntax is:
=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1, “Distinct row”, “”)
Be careful with the dollar signs.
To distinguish distinct rows, repeat every single step in the Show Distinct values section, and add the second data column just like you did the first one. (See the yellow box in figure 7) In addition, you must use the CountIfs function instead of the CountIf.
The Easiest Way to Count Distinct and Unique Values
Now to count these values, you can easily use the Countif formula. For example, to count the Distinct values follow these steps:
- Show your data as Unique or Distinct values by the steps we mentioned.
- Select a cell and enter the Countif formula.
- The formula is
=countif(data range1, data range2[optional],…, “Distinct”)
- Now you can see how many Distinct values you have.
Count Unique and Distinct Values by Colour
Using this technique in Excel allows you to distinguish values by color and be able to count them easily. So let’s try it. To count Unique values, you can use the default rule. Having said that, for the Distinct values, we have to write the formula because there is no default rule in Conditional Formatting.
Count Unique Values by Conditional Formatting
Follow these steps to determine your unique value in a blink of an eye.
- Select your data set.
- Go to the Home tab.
- In the Style section, click the Conditional Formatting button.
- Click the Highlight Cells Rules.
- Select the Duplicate values from the menu.
- From the Duplicate values dialogue box, select Unique and choose the format that fills the cells by color (for example, Light red fill with dark red text.)
Count Distinct Values by Conditional Formatting
To determine your distinct value we follow steps 1, 2, and 3 in previous section, then do these steps:
- Click the New Rule.
- Select the Use a formula to determine which cells to format from the New formatting rule dialogue box.
- Enter this formula.
=COUNTIF($A$2: $A2, $A2)=1
- For the Preview section, click the Format button, then you can format cells by Number, Font, Border, and fill tabs.
- Press OK.
If you want to colour a row, select your dataset after following the previous steps, go to Conditional Formatting, and click Manage Rules and select your range In the Applies To section of the Conditional Formatting Rules Manager dialogue box. Then press OK.
Count Coloured Cells
Now for counting the colored cells follow these steps. There are a few ways to count colored cells. Here, we’ll count them using the Auto filter option.
- Select your data set (select your heading too.)
- Go to the Home tab in the Editing section, click on the Sort and Filter, then click Filter (through these steps, you apply a filter to your data set.)
- Use the SubTotal function to count our data. Select a cell and enter the function.
=subtotal(102, data range)
- Then click on the drop-down list filter.
- Select Filter by colour.
- Choose the colour you want to count. As you can see, we choose the light red.
Count Unique and Distinct Values by Sum and If
The most common way to count these values is using a combination of SUM, IF, and COUNTIF functions. Describing how these functions work for Unique values starts with the COUNTIF that specifies the repeated number for each value. Then the IF function assumes unique values as one and replaces duplicate values with zero. The Sum function sums the ones, and the final number is the number of unique values.
So, to count your Unique value, use this array formula.
=SUM(IF(COUNTIF( data range, data range)=1,1,0)))
Point: After you enter the array, you must press Shift+Ctrl+Enter.
You can count Distinct values according to your data set. For example, sometimes your data set contains empty cells or includes numbers and text.
So, to count your Distinct value, use this array formula.
=SUM(IF(range<>””,1/COUNTIF(range,range), 0))
Point: After you enter the array, you must press Shift+Ctrl+Enter.
Count Numerical and Text Values
Sometimes you have either text or number in your data set. In this case, you should add the ISTEXT or ISNUMBER functions to your array formula.
Count Unique Text Values
We count Unique text values by this array.
=SUM(IF(ISTEXT(data range)*COUNTIF( data range, data range)=1,1,0)))
Point: After you enter the array, you must press Shift+Ctrl+Enter.
This formula doesn’t count empty cells, numbers, logical values and errors.
Count Distinct Text Values
We count Distinct text values by this array.
=SUM(IF(ISTEXT(range),1/COUNTIF(range,range),””))
Point: After entering the array, you must press Shift+Ctrl+Enter.
Note that this formula doesn’t count empty cells, numbers, logical values, and errors.
Count Unique Numerical Values
We count these values in a mixed data set by this array. This array formula is similar to the previous one, but we use the ISNUMBER function.
=SUM(IF(ISNUMBER(data range)*COUNTIF( data range, data range)=1,1,0)))
Point: After you enter the array, you must press Shift+Ctrl+Enter.
This formula doesn’t count empty cells, text, logical values and errors.
Count Distinct Numerical Values
We can count the values in a mixed data set by the following array. This array formula is similar to the previous one except for the ISNUMBER function.
=SUM(IF(ISNUMBER(range),1/COUNTIF(range,range),””))
Point: After entering the array, you must press Shift+Ctrl+Enter.
Again, note that this formula doesn’t count empty cells, text, logical values, and errors.
How to Count Unique and Distinct Values With Case Sensitivity?
In some data sets, values may have capital letters which makes them different from other values. For example, assume a list of materials. Type a on this list is different from type A. To count unique values in this data set, we use these formulas.
- To show which values in your data set are Unique, use this formula in a new column.
=IF(SUM(–EXACT($data rang$, first value)))=1, “Unique”, “”)
Point: After you enter the array, you must press Shift+Ctrl+Enter.
- Select an empty cell and enter this formula to count the Unique ones.
=COUNTIF(the newly formed column, “Unique”)
The formula to count Distinct values by this method is the same as the Unique values formula, except that there is a slight difference in the cell address:
- To show which values are Distinct in your data set, use this formula in a new column.
=IF(SUM((–EXACT($A$2:$A2,$A2)))=1,”Distinct”,””)
Point: After entering the array, you must press Shift+Ctrl+Enter.
A2 is the first value in our example. Pay attention to the dollar signs.
- Select an empty cell and enter this formula to count the Distinct ones.
=COUNTIF(the newly formed column, “Distinct”)
In this blog, we learned what Distinct and Unique values are and how to show and count them in a data list by the conditional formatting, CountIf formula, and Sum&If formula. If you need more help with counting formulas, visit Excel Count Functions.
You can connect with us, ask our experts if you have any inquiries and get more support via Excel Support Services.
Also, reduce costs, accelerate tasks, and improve quality with Excel Automation Services.
FAQs
Does Excel have Countunique? ›
To help you count unique values, Excel offers the UNIQUE function. Array is the range or array to be evaluated. By_col (optional argument) tells Excel whether the data in the array is displayed row-by-row or column-by-column.
Does Countif count unique values? ›The COUNTIF function counts how many times each individual value appears in the specified range. In this example, COUNTIF(A2:A10,A2:A10) returns the array {1;2;2;1;2;2;2;1;2} . The IF function evaluates each value in the array returned by COUNTIF, keeps all 1's (unique values), and replaces all other values with zeros.
How do I count distinct words in Excel? ›- Enter the formula =SUM(IF(COUNTIF(range, range)=1,1,0)) in the desired cell to count unique values. ...
- Think of the case from above. ...
- Enter the cells containing the elements whose unique value is to be sought in place of range. ...
- Press Ctrl+Shift+Enter to continue.
- We imported numpy as np and created an array using the array() function.
- We used the unique() function from numpy to remove any duplicates.
- Finally, we calculated the length of that array.
...
Counting Duplicate Rows in Excel
- Select the cell F2 by clicking on it.
- Assign the formula =COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2,$C$2:$C$8,C2) to F2.
- Press Enter.
COUNTA counts all values in a dataset, including those which appear more than once and text values (including zero-length strings and whitespace). To count unique values, use COUNTUNIQUE . To count only numeric values use COUNT .
Can distinct and count be used together? ›Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table.
Does Excel 365 have distinct count? ›To use Distinct Count option, one should add the data to Data Model. We can insert PivotTables in Excel for the web spreadsheet but to create Power Pivot data models, we will need Excel desktop application. Distinct Count option is available of Excel 365 desktop application, but not in online version.
Can you Countif 2 conditions? ›You can use the COUNTIFS function in Excel to count cells in a single range with a single condition as well as in multiple ranges with multiple conditions. If the latter, only those cells that meet all of the specified conditions are counted.
Can you use Countif for 3 criteria? ›Excel has many functions where a user needs to specify a single or multiple criteria to get the result. For example, if you want to count cells based on multiple criteria, you can use the COUNTIF or COUNTIFS functions in Excel.
How do you get distinct characters and counts in a string? ›
In order to get the distinct element and their count in a string in Java, we will use the Java HashMap . We will convert the string into uppercase or lowercase for our convenience.
How do I extract a unique list and duplicates from one column in Excel? ›- Click the Advanced Filter button on the Data tab of the Ribbon.
- Select the “Copy to another location” radio button.
- Select the “List range”. The range/column that contains the duplicate values.
- Select the “Copy to” range. ...
- Click the “Unique records only” checkbox.
- Click the OK button.
Select Text option from the Formula Type drop down list; Then choose Extract cells with unique values (include the first duplicate) from the Choose a fromula list box; In the right Arguments input section, select a list of cells that you want to extract unique values.
Is there an Excel formula for duplicates? ›The duplicate-checking formula uses =COUNTIF to “count” which cells contain data that appears more than once throughout the spreadsheet. Resulting values can either be “TRUE” (indicating duplicate data) or “FALSE” (showing non-duplicate data).
How do I use Countif to find duplicates in two columns? ›...
To find out whether the names in column B are duplicates, we need to:
- Go to cell C2.
- Assign the formula =AND(COUNTIF($A$2:$A$6, A2),COUNTIF($B$2:$B$6, A2)) in C2.
- Press Enter.
The CountA function counts the number of records that aren't blank in a single-column table. This function includes empty text ("") in the count. The CountIf function counts the number of records in a table that are true for a logical formula. The formula can reference columns of the table.
Can I use Counta and Countif together? ›We can use a combination of the COUNTA, COUNTIF, and SUMPRODUCT functions to get the desired results. We can list down the things we wish to exclude from counting. One other way to arrive at the same result is to use the formula =COUNTIFS(B4:B9,”<>Rose”B4:B9,”<>Marigold”).
How do I count the number of unique values in sheets? ›The COUNTIFS function can be used to count the number of unique values in a range of cells that match a specific set of criteria. The COUNT function can be used to count the number of unique values in a range of cells. The DISTINCT function can be used to count the number of unique values in a range of cells.
What can we use instead of distinct? ›Some common synonyms of distinct are apparent, clear, evident, manifest, obvious, patent, and plain.
Is unique and distinct are same? ›“Distinct” means total number of different values regardless how many times it appears in the dataset. A name appears in the list multiple times is counted as 1 distinct count. Whereas, the “Unique” value is total number of values that only appear once.
What is the difference between distinct and distinct count? ›
However, the key difference between the DISTINCT function and DISTINCTCOUNT functions lies in how they work. The DISTINCTCOUNT function already has the built-in 'COUNT' which is defined by its name. Meanwhile, the DISTINCT function cannot be used to return values into a cell or column on a worksheet.
What is the fastest way to count duplicates in Excel? ›Tip: If you want to count the duplicates in the whole Column, use this formula =COUNTIF(A:A, A2) (the Column A indicates column of data, and A2 stands the cell you want to count the frequency, you can change them as you need).
Is there a count if function? ›Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.
Does Countifs use and/or or logic? ›As I mentioned earlier, COUNTIFS uses AND logic and on the other hand, in COUNIF you can only specify one value to count. To understand the application of OR with these functions you need to split this formula into three parts. First of all, we have used two different criteria in this formula by using array concept.
How do you put 3 conditions in if Excel? ›- Method 1: Nested IF Function =IF(C2<15, "Bad", IF(C2<20, "OK", IF(C2<25, "Good", "Great")))
- Method 2: IF Function with AND Logic =IF(AND(A2="Mavs", B2="Guard", C2>25), "Yes", "No")
Countif filtered data with criteria by Excel functions
In a blank cell enter the formula =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)),ISNUMBER(SEARCH("Pear",B2:B18))+0), and press the Enter key. Formula is too complicated to remember?
A unique string consists of characters that occur only once. To check for uniqueness, compare each character with the rest of the string. If a character is repeated, then the string is not unique.
How do you count occurrences of multiple characters in a string? ›- Find the occurrences of character 'a' in the given string.
- Find the No. of repetitions which are required to find the 'a' occurrences.
- Multiply the single string occurrences to the No. ...
- If given n is not the multiple of given string size then we will find the 'a' occurrences in the remaining substring.
- Create a inner class with required variables.
- Create a List of the inner class created above and dont forget to encapsulate it.
- Get the value stored to the list as a inner class object.
- create a DATAMODEL and push the list to the datamodel.
- get the warpped data to another list.
Count how often a single value occurs by using the COUNTIF function. Use the COUNTIF function to count how many times a particular value appears in a range of cells.
How do you dynamically extract a list of unique values from a column range in Excel? ›
- Go to Data tab in the menu.
- In Sort and Filter box, Click Advanced button.
- Choose "Copy to another location"
- In "List range :" box, select a range from which unique values need to be extracted (including header)
- In "Copy to :" box, select a range in which final output to be put.
- Select a cell in your dataset and go to Data > Advanced filter to the far right. ...
- Choose to “Filter the list, in-place” or “Copy to another location”. ...
- Tick the “Unique records only” box to keep the unique values, and then “OK” to remove all duplicates.
- 1) Using count() method. count() is the in-built function by which python count occurrences in list. ...
- 2) Using a loop. ...
- 3) Using countof() method. ...
- 4) Using counter() method. ...
- 5) Using pandas library. ...
- 6) Using loops and dict in python.
You can use the COUNTIFS function in Excel to count cells in a single range with a single condition as well as in multiple ranges with multiple conditions. If the latter, only those cells that meet all of the specified conditions are counted.
How do I automatically count frequency in Excel? ›Note: You also can use this formula =COUNTIF(A1:A10,"AAA-1") to count the frequency of a specific value. A1:A10 is the data range, and AAA-1 is the value you want to count, you can change them as you need, and with this formula, you just need to press Enter key to get the result.
Does count (*) include duplicate values? ›Answer. Yes, when using the COUNT() function on a column in SQL, it will include duplicate values by default. It essentially counts all rows for which there is a value in the column. If you wanted to count only the unique values in a column, then you can utilize the DISTINCT clause within the COUNT() function.