How to Count Unique Values in Multiple Columns in Excel (5 Ways)

There is no easy way to compute the number of unique values in Excel. That’s why we need to use formulas and do some manual tasks in Excel to Count Unique Values in Multiple Columns. Throughout this article, we will guide you through several ways to count unique values in multiple columns. We will use a sample dataset containing Name, Profession, Age, and Region.

How to Count Unique Values in Multiple Columns in Excel: 5 Ways

We will guide you with Pivot table and functions like IF, SUMPRODUCT, INDIRECT, and a formula with a combination of functions.

Method 1: Count Unique Values in Multiple Columns by IF Function

In our first method, we will see the use of the IF function. Let’s see how the function helps us to count unique values across multiple columns.

Steps:

• First, click on cell G5, and type the following formula.

• Now, press the ENTER key.

• After that, drag down to AutoFill the series as shown in the next image.

Here, we have set the IF function in such a way that for every second appearance of data in multiple columns, it will give zero (0) as a result.

• Now, click on the blank cell in the Total Unique column and type the following formula.

• Finally, press the ENTER key.

Done.

Method 2: SUMPRODUCT Function to Count Unique Values in Multiple Columns

In the earlier section, we got the final result by using two formulas. If you want to count it within a single-step formula, then the SUMPRODUCT function may come in handy. SUMPRODUCT will give us the total unique number directly.

Steps:

• First, click on cell G5, and type the following formula.

• At last, press the ENTER key.

Here, the SUMPRODUCT function will give us the total result of the unique values. If you want to see the insights of what we get by dividing 1 by COUNTIFS portion following image will give you a better idea.

For the unique values, it provides 1, and for duplicates, a fraction value. Then, SUMPRODUCT does the rest.

Read More: Excel SUMPRODUCT Function to Count Unique Values with Criteria

Method 3: Count Unique Values in Multiple Columns Using INDIRECT Function

Suppose we have a different kind of dataset where we have a list of names in three different columns. We want to extract unique values from these columns. Let’s count the uniques in these columns.

Steps:

• First, type the following formula in cell F5.

• Now, press, the CTRL+SHIFT+ENTER key altogether (if you are using any version except Excel 365.

After that, drag down till blank cells appear.

• After that, click on cell H5 and type the following formula.

• Finally, press the ENTER key.

That’s it.

Related Content: How to Count Unique Values Based on Criteria in Another Column in Excel

Similar Readings:

• How to Use COUNTIF for Unique Text
• COUNTIFS Unique Values in Excel

Method 4: Pivot Table to Count Unique Values in Multiple Columns

A Pivot Table is another way to count unique values in multiple columns.

Steps:

• First, go to the INSERT tab and Click PivotTable.

• Now, select the table range and cell where you want your pivot table (here, we have chosen the existing sheet, you can go to a new sheet as well), and click OK

• Here, a Field List will pop up and do as the following image.

• After that, click on the drop-down arrow and select Value Field Settings.

• As a result, a new dialogue box will pop up and select Distinct Count.

• Now, do the same for Count of Profession.

• Finally, our pivot table will look like the following.

Now, we can easily use the COUNTA or SUM function to do the total count of unique values as described in the previous methods.
Our final Image will look like this.

Method 5: A Combined Formula to Count Unique Values in Multiple Columns

In our last methods, we see a combination of several functions to do our task. We’ve made some minor changes to our dataset, and want to count unique values in Name and Vote Columns.

Steps:

Here, we will use Conditional Formatting.

• First, select the data range and click New Rule in the Conditional Formatting Tab.

• At this point, a dialogue box will pop up and type the formula as shown in the image below, and click Format.

• After that, select the color you want.

• At this point, click on the blank cell G5, and go to the Data tab, and select Data Validation as shown in the following image.

• Now, a dialogue box will pop up and do as the following image says and click OK.

• After that, click on cell G9, and type the following formula.

• Now, press the CTRL+SHIFT+ENTER key.

• Now, select Yes from the drop-down.

We will get the result as follows.

That’s it.

Read More: Excel VBA: Count Unique Values in a Column

Practice Section

The single most crucial aspect of becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you can practice these methods.

Download Practice Workbook

Conclusion

These are 5 different methods for  Excel Count Unique Values in Multiple Columns. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback

Related Articles

• Count Unique Values with Criteria by COUNTIFS in EXCEL
• How to Count Unique Text Values with Criteria in Excel
• How to Count Unique Values in Filtered Column in Excel
• How to Count Unique Names in Excel
• How to Count Unique Values in Excel with Multiple Criteria