Working in Excel with a lot of data may be difficult. Some values allow for several repetitions and make us go through the process of how to count unique text values in excel. When doing any function, you might need to take into account many data entries in order to increase your accuracy. You must count the values in order to organize the information or even to get statistics from it. With such a large volume of data, manually counting the values would be incredibly challenging. It could be particularly challenging to remember different or special values. Excel provides numerous ways to count various and distinct data and it also tells us about how to count unique text values in excel.
How to count unique text values in excel online, 2016 and 2019:
Let’s start by taking a look at how to count unique data in Excel.
Using SUM, IF and Count functions in excel:
Any operation can be solved at any moment using Excel functions. In this case, you can combine the SUM, IF, and COUNTIF functions in Excel to count unique data.
- To count distinct values, enter the formula =SUM(IF(COUNTIF(range, range)=1,1,0)) in the desired cell. The range designates the first cell and the last cell.
- This array formula maintains the count values in a distinct array. This is an array formula, therefore be cautious to hit Ctrl+Shift+Enter after inputting the formula. Also keep in mind that when you enter the formula, curly brackets will be automatically placed at the conclusion. Don’t manually enter them, though.
- Consider the scenario from earlier. To find the unique values in the attached Excel sheet, enter the formula in the target cell.
- Instead of using range, enter the cells holding the elements whose unique value is to be sought. Cells B3 to B16 in this picture house the mentioned parts. The equation is thus:
=SUM(IF(COUNTIF(B3:B16, B3:B16)=1,1,0))
- To proceed, press Ctrl+Shift+Enter. This shows how many unique values there are in the selected range. There are three distinct values.
Count unique text values in excel:
- Some tables and workbooks allow for the combination of text and numbers. If so, you can adapt the aforementioned function to search for the unique text values in Excel.
- Type =SUM(IF(ISTEXT(range)*COUNTIF(range,range)=1,1,0)) in the target cell and press Ctrl+Shift+Enter. The range designates the start and end cells that house the items.
- The ISTEXT element has been added to the general formula in order to determine the specific text values. If the value is a text, the ISTEXT method returns 1 and counts the value in an array. If the cell has a non-text value, it returns 0.
- The operation is similar to the generally accepted 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.
