How to count unique text values in Excel?

Handling a lot of data that contains duplicate entries can become difficult when using Excel. It’s better to find out how many unique and duplicate entries are present in a given dataset to ensure that you can perform analysis the right way. 

The given scenario of duplicate entries can get troubling if you deal with numeric values to find statistics. But fret not; there are quick ways to separate unique values in Excel, which we will discuss in this blog – keep reading!

What are Unique Values in Excel?

A given set of data can be categorized based on the repeated values it contains. If there are no repeated values in the given data, then it will be labeled as unique data. But data containing repeated values will be labeled as distinct data.

In simple terms, it can be said that unique data values are present only once throughout the given dataset. But when we call a data set to be distinct data, we mean that it contains values that might be repeated.

What is Distinct vs. Unique COUNT in Excel?

The count feature is used to find the total number of values. Whether you are going to deal with unique values or distinct values, you can use the COUNT function to find the number of values easily.

How to Count Unique Text Values in Excel 

 Let's look at how to count unique data in Excel first.

Using SUM, IF, and COUNTIF Functions in Excel

The best way you can count the total number of unique values in a given dataset is by using the SUM, IF, and COUNTIF functions as a combination. With this formula, you can define a range within a sheet and easily find the values that are unique or distinct.

The general syntax for this formula is given as follows:

=SUM(IF(COUNTIF(range, range)=1,1,0))

Here is how you can use this formula:

• Start by opening your desired spreadsheet in the WPS Office.

  
  

  Free Download

• Enter the formula given above at the end of the column you are going to inspect for the purpose of finding unique and distinct values. In our case, the empty cell we are choosing is Cell B31.

In our case, we are also going to modify the formula entered on Cell B31 as follows:

=SUM(IF(COUNTIF(B4:B29,B4:B29)=1,1,0))

Notice that there are Curly Brackets at the beginning and the end of the formula. You don’t have to enter these brackets manually as they are applied by the WPS Office itself.

• After entering the formula, use the Ctrl+Shift+Enter combination instead of pressing Enter alone. Doing so will ensure that the formula fetches the right values:

You can check that Three, Two, and Five are the only unique values that appear in our data once. Using this formula has now returned that there are only three unique values in our given data.

If you are wondering about how this formula works, then you can read how the combination does the following job:

• The formula first executes the COUNTIF command to find the number of repeated values.

• Then, it applies the IF command to filter out any unique values.

• The SUM is applied finally to calculate all unique entries.

Count Unique Text Values in Excel

In some cases, you might need to work with data that contains text values. If you want to follow the sum of unique text values in a given dataset, then you can use the formula discussed above with some changes.

Here is the general syntax for finding unique text values in Excel:

=SUM(IF(ISTEXT(range)*COUNTIF(range,range)=1,1,0))

This is an example to help you understand how the formula works:

• Open your desired spreadsheet in the WPS Office.

• Locate an empty cell at the end of the column in which you want to find unique text values.

• Enter the formula by defining the range. In our case, we will use this formula:

By using the Ctrl+Shift+Enter combination, you will see the following result in Cell B31 of the sheet:

In this formula, the ISTEXT function is used to instruct Excel that you are dealing with unique text values only. The rest of the entries of the formula are the same as described above.

Count Unique Numeric Values in Excel

There might be instances when you only need to find out the number of unique numeric values in a given dataset. For example, if you are willing to find specific marks obtained by a student, then you might have to deal with numeric values only.

In such a case, your best option is to use the formula we are going to discuss here. This formula is also an alteration of the original formula we have used for finding unique values above.

Here is the general syntax of the formula:

=SUM(IF(ISNUMBER(range)*COUNTIF(range,range)=1,1,0))

This example will help you understand how you can use this formula.

• Start by opening your desired sheet in the WPS Office.

• Choose an empty cell at the end of the range you are going to inspect for finding unique numeric values. In this case, we are going to choose Cell C31.

• After that, we will enter the formula in C31 by modifying it. Here is what our modified formula looks like:

By using the Ctrl+Shift+Enter combination, you will get this result in Cell C31:

You can check that the formula has returned the sum of three unique numeric values in the given range. The only three unique numeric values are 3,4, and 5, respectively. The ISNUMBER component of the formula is for showing that we are only interested in numeric values.

How to Count Unique Values with Multiple Criteria

You can use excel count unique values multiple criteria by following the tips we have mentioned here:

• Start your process of finding unique values that satisfy multiple criteria by using this formula:

= IFERROR(ROWS(UNIQUE(range, (criteria_range1=criteria1) * (criteria_range2=criteria2)))), 0)

• Make sure that your data is sorted properly in all the columns. You can check this example to learn how your data should be sorted:

• Now, proceed by modifying the range in the generic formula mentioned above:

• End the process of finding unique values by using the Ctrl+Shift+Enter combination:

Using this formula for finding unique values with multiple criteria can be a bit tricky if you put the formula in the wrong cell or don’t define the range properly. This is why you should ensure that there are no mistakes in your given dataset or formula.

How to Use Count Function in Excel

You can use the excel count unique values criteria by following the tips mentioned below:

• Launch your desired spreadsheet that contains data on which you want to use the count function. In the “Formulas” tab, use the “Insert Function” feature.

• From the dropdown menu screen in the software, click on the “COUNT” option to get started:

• Next, you will be asked on the splash screen to enter the “Value1” and “Value2” in the software. Select the cells of the column to get the range for the formula so you can use the COUNT function:

• Once you provide the range, you will be able you see the result of COUNT function in your Excel spreadsheet:

How to Count Cells with Text in Excel Using Formula

You can use excel count unique values using formula by following the simple tips mentioned below:

• Start by entering the generic formula for counting all the cells that contain some text value:

COUNTIF(range, *)

• Modify the range of the COUNTIF formula to get your desired result:

• You will now see the total number of all the cells that have some text value in them.

How to Count Names on Excel without Duplicates

You can use excel count unique values feature to find the count of the names in an Excel dataset without dealing with duplicates:

• Start by using the generic syntax of COUNTA function:

=COUNTA(range)

• Choose an empty cell in the Excel sheet to find the number of counts.

• Go to the “Formulas” tab to choose the “COUNTA” function:

• Enter the range from which you want to find the values by filling the Value1 field. In our case, the

range is as given below: 

• This will be the desired result in the Excel sheet:

WPS Office - Free Alternative to Microsoft Office

You can use excel count unique values feature and do a lot more by using WPS Office. The good thing about WPS Office is that besides helping you meet all your spreadsheet needs, it can also help you with creating documents, spreadsheets, and presentations.

Trustpilot4.8

WPS Office- Free All-in-One Office Suite

• Use Word, Excel, and PPT for FREE, No Ads.
• Edit PDF files with the powerful PDF toolkit.
• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.
• Microsoft-like interface. Easy to learn. 100% Compatibility.

Free Download

5,820,008 User

Algirdas Jasaitis

FAQs

Q1. What Is Distinct Vs. Unique COUNT In Excel?

Before using excel count unique values, it’s better to know the difference between Distinct and Unique values.

In simple terms, any dataset in a spreadsheet will be called Unique data if it doesn’t contain repeated values. But a dataset in a spreadsheet will be labeled as Distinct data if it has repeated values that show up throughout a dataset or in a certain section of the data.

Q2. How to Count Distinct Values in Excel?

1. Here is the step-by-step process for counting distinct values in Excel:

2. Choose the range of cells in which you want to find distinct values.

3. Go to the Data tab in Excel and choose Advanced from the Sort & Filter field.

4. On the next screen, choose the “Copy to another location” option.

5. Check the List Range and Criteria Range in the dialog box.

6. Don’t forget to check the “Unique records only” button and proceed by clicking “OK.”

7. You will now see the count of unique values in Excel.

Q3. How to Use COUNTIFS Formula with Multiple Criteria, Counting Unique Values?

You can learn to use excel count unique values feature by using COUNTIFS formula and multiple criteria by looking at this example:

Open the desired spreadsheet in Excel in which you want to count unique values that satisfy multiple criteria. For example, we start working with this sheet:

You can use this formula to get your desired result:

=SUM(N(IF(($B$2:$B$9="Alberta")*($D$2:$D$9"grant"),MATCH(IF(($B$2:$B$9="Alberta")*($D$2:$D$9"grant"),$C$2:$C$9),IF(($B$2:$B$9="Alberta")*($D$2:$D$9"grant"),$C$2:$C$9),0)=ROW(1:8))))

Q4. How to Count Unique Values in Pivot Table?

You can use the excel count unique values feature in a pivot table by following these instructions:

• Start by creating a new helper table in your spreadsheet. Make sure you use this formula as well:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

• Once you put the formula in one cell of the spreadsheet, you can drag the handle and fill the formula in all cells.

• Now, proceed by inserting a pivot table:

• Follow these instructions when creating a new pivot table:

• Drag Column A of the sheet to the Row Labels and Column C to Values box, respectively to see the sorted unique values in a new pivot table:

Count Unique Values with WPS Office

You can count unique values with Excel, but the Microsoft Excel software can be a bit difficult to use. It’s better to save your time and effort by using better software like WPS Office to run operations on your data easier than ever before.

The good thing about working with WPS Office is that it is compatible with all leading operating systems like Mac, Windows, Android, iOS, and Linux. This office suite is trusted by more than 500 million users worldwide for its great features.