How to count unique text values in Excel?

July 26, 2022
5.6K Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

It might be challenging to work with a lot of data in Excel. Several repetitions of some values are possible. To improve your accuracy, you might need to consider numerous data entries when conducting any function. To organize the data or even to get statistics from it, you will also need to count the values. Manually counting the values would be extremely difficult with enormous amounts of data. It might be particularly difficult to keep track of distinct or unique values. Excel offers a variety of methods for counting different and unique data.


How to count unique text values in excel online, 2016 and 2019

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


Using SUM, IF and Count functions in excel

Excel functions can be used to solve any operations at any time. To count unique values in Excel in this situation, combine the SUM, IF, and COUNTIF functions.

1.Enter the formula =SUM(IF(COUNTIF(range, range)=1,1,0)) in the desired cell to count unique values. The initial cell and the final cell are indicated by the range.The count values in this array formula are kept in a separate array. Make careful to use Ctrl+Shift+Enter after entering the formula because this is an array formula. Also take note that curly braces will be added automatically at the end of the formula when you enter it. But don't manually enter them.



2.Think of the case from above. Enter the formula in the target cell to determine the distinct values in the provided Excel sheet.

3.Enter the cells containing the elements whose unique value is to be sought in place of range. The stated pieces are housed in cells B3 to B16 here. Thus, the equation is:=SUM(IF(COUNTIF(B3:B16, B3:B16)=1,1,0))

4.Press Ctrl+Shift+Enter to continue. This displays the number of distinct values within the chosen range. The number of unique values is 3.

Count unique text values in excel

1.Text and numbers may be combined in some tables and workbooks. 

2.In these circumstances, you can modify the aforementioned function to find the distinct text values in Excel.In the target cell, type =SUM(IF(ISTEXT(range)*COUNTIF(range,range)=1,1,0)) and click Ctrl+Shift+Enter. 

3.The start and end cells that contain the elements are indicated by the range.To determine the specific text values, we have added the ISTEXT element to the generic formula. 

4.The ISTEXT method returns 1 and counts the value in an array if the value is a text. It returns 0 if the cell has a non-text value.The functionality is comparable to the widely used formula above.


Note: This above written article is an attempt to show you how to count unique text values in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.