Counting distinct values in Excel is a common task that many users encounter while working with spreadsheets. Whether you need to identify unique text values or find the number of distinct values based on certain criteria, Excel provides several methods to accomplish this.
In this article, we will explore three easy ways to count distinct values in Excel, allowing you to efficiently analyze your data and gain valuable insights.
What Is Distinct Count in Excel?
Distinct count in Excel refers to the number of unique values within a specified range of data, excluding any duplicates. It provides a count that focuses solely on the distinct values present.
This metric is valuable when dealing with datasets that have repetitive information, allowing for a clearer analysis of unique values. It differs from the simple COUNT function in that the COUNT function includes all values, including duplicates, while distinct count focuses solely on unique values.
How to Count Distinct Values Using Filter Option
The first method we'll discuss is using the Advanced Filter option in Excel, which allows you to identify and count unique text values within a column by copying them to another location. To understand this, let's consider the following dataset of 15 material codes:
Step 1: Select the column containing the values you want to count.
Step 2: Go to the Data tab in the Excel ribbon and click on the Advanced button in the Sort & Filter group.
Step 3: In the Advanced Filter dialog box, choose the option "Copy to another location." Then, make sure to check the box labeled "Unique records only" to copy only the distinct values. Next, specify the destination range where you want to copy the unique values. For instance, in this example, I will copy the list to column C, starting from cell C1.
Step 4: Click OK to apply the advanced filter and copy the unique values to the specified range.
Step 5: Now you can use the ROW(range) function to count the distinct values.
And that's how you can count the distinct values using the Filter option.
How to Count Distinct Values Using Formulas
Excel provides powerful formulas that can help you count distinct values in both text and numeric formats. Let's explore two commonly used formulas for this purpose.
Finding Distinct Text Values:
You can use the following formula to count unique text values in Excel:
=SUM(IF(ISTEXT(range),1/COUNTIF(range,range),””))
To apply this formula, follow these steps:
Step 1: Select an empty cell where you want to display the distinct count.
Step 2: Replace "range" in the formula with the actual range of cells containing the text values you want to count.
Step 3: Press Ctrl + Shift + Enter to enter the formula as an array formula. Excel will automatically calculate the distinct count based on the provided range.
Finding Distinct Numeric Values:
Similarly, if your data is numeric, you can count unique numeric values using the following formula:
=SUM(IF(ISNUMBER(range),1/COUNTIF(range,range),””))
Follow the same steps as above, replacing "range" with the range of cells containing the numeric values you want to count.
How to Count Distinct Values Using a Pivot Table
Pivot tables offer a convenient way to analyze data in Excel, including counting distinct values. Here's how you can achieve this using a pivot table:
Step 1: Select the range of data you want to analyze.
Step 2: Go to the Insert tab and click on the PivotTable button. In the dialog box, choose your data source. For example, if your data is in the form of a Table, select From Table/Range.
Step 3: In the following window, Excel will automatically retrieve your table if you selected it in the previous step. Now, you can specify the location for your PivotTable. If you prefer to place it in the existing worksheet, indicate the cell where you want your pivot table to be located. For instance, in my example, I have chosen to position my table at cell C1. Make sure to check “Add this data to the data model”.
Step 4: In the PivotTable Fields pane, drag the column that contains the values you want to count into the "Values" area.
Step 5: By default, Excel will summarize the values using the "Count" function. To change the summary function, click on the drop-down arrow next to the field name in the Values area and select "Value Field Settings".
Step 6: Choose "Distinct Count" from the list.
Step 7: Click OK to see the distinct values.
Best Alternative to Microsoft Excel - WPS Office
While Microsoft Excel is widely used, there are alternatives available that offer similar functionality. One such alternative is WPS Office. Following are the reasons why you should opt for the WPS office.
Compatibility: WPS Office ensures seamless compatibility with Excel's essential features, formulas, and macros, allowing for smooth transition and work continuity.
Cost-effectiveness: WPS Spreadsheet provides a free version equipped with most essential features, offering an economical solution for users. Additionally, it offers a premium version at a lower price point compared to Microsoft Excel, giving you excellent value for your money.
User-Friendly Interface: With its user-friendly features and intuitive navigation, WPS Office makes spreadsheet management a breeze. Whether you're a beginner or an experienced user, you'll find it easy to navigate and utilize the tools efficiently.
FAQs
Q1. What’s the difference between distinct and unique counts in Excel?
A: In Microsoft Excel, distinct counts represent the quantity of unique values within a dataset, regardless of whether they are duplicates or not. On the contrary, unique counts encompass only those items that do not have any duplicates.
Q2. How to count unique values in multiple columns in Excel?
A: To count unique values across multiple columns, you can use the COUNTIFS formula. Specify each column range and the corresponding criteria to ensure the count is based on the desired conditions.
Q3. Why is distinct count not working in Excel?
A: If the distinct count is not working in Excel, ensure that you are using the correct formulas or methods. Check for any hidden or filtered data that may affect the results. Additionally, confirm that your data is formatted correctly, as distinct count may not work as expected with improperly formatted data.
Learn Distinct Value Counting in Excel with Ease
Handling distinct values can sometimes be confusing, but with the convenience of Excel, this task becomes straightforward. In this article, we explored how Excel can efficiently assist in counting distinct values, making your job quicker and more manageable. WPS Spreadsheet offers a user-friendly interface, allowing you to accomplish this task effortlessly. Download WPS Office today and get the hang of excel effortlessly.
