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 with several ways to **count unique values in multiple columns**. We will use a sample dataset containing **Name**, **Profession**, **Age**, and **Region**.

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Count Unique Values in Multiple Columns in Excel

We will guide with **Pivot table** and functions like **IF**, **SUMPRODUCT**, **INDIRECT**, and a formula with 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.

`=IF(COUNTIFS($B$5:$B5,B5,$D$5:D5,D5)=1,1,0)`

- 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.

`=SUM(G5:G13)`

- Finally, press the
**ENTER**key.

Done.

**Read More: Excel Formula Count Unique Values (3 Easy Ways)**

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

In the earlier section, we have 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 the cell
**G5**, and type the following formula.

`=SUMPRODUCT((1/COUNTIFS(B5:B13,B5:B13,C5:C13,C5:C13)))`

- At last, press
**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: Count Unique Values with Criteria by SUMPRODUCT in Excel**

### 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 unique from these columns.

**Steps:**

- First, type the following formula in cell
**F5**.

`=INDIRECT(TEXT(MIN(IF(($B$5:$D$13<>"")*(COUNTIF($F$4:F4,$B$5:$D$13)=0),ROW($5:$13)*100+COLUMN($B:$D),7^8)),"R0C00"),)&""`

- 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.

**Formula Breakdown**

**IF(($B$5:$D$13<>””)*(COUNTIF($F$4:F4,$B$5:$D$13)=0),ROW($5:$13)*100+COLUMN($B:$D),7^8)**yields the result**{502,503,504;602,603,604;702,703,704;802,803,804;902,903,904;1002,1003,1004;1102,1103,1104;1202,1203,1204;1302,1303,1304}**

**TEXT(MIN(IF(($B$5:$D$13<>””)*(COUNTIF($F$4:F4,$B$5:$D$13)=0),ROW($5:$13)*100+COLUMN($B:$D),7^8)),”R0C00″)**gives us the result**“R5C02”**

- Finally
**=INDIRECT(“R5C02″,)&””**gives us the result as**James**.

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

`=COUNTA(F5:F23)`

- 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 (8 Easiest Ways)****COUNTIFS Unique Values in Excel (3 Easy Ways)****How to Count Unique Names in Excel (5 Methods)**

### 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 **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.

**Read More:** **How to Count Unique Values in Excel Using Pivot Table**

### 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.

`=SUM(IF(FREQUENCY(IF(C5:C13=G5,MATCH(B5:B13,B5:B13,0)),ROW(B5:B13)-ROW(B5)+1),1))`

- Now, press the
**CTRL+SHIFT+ENTER**key.

**Formula Breakdown**

**IF(C5:C13=G5,MATCH(B5:B13,B5:B13,0))**checks the value from**G5**value within**C5:C13**range and provides the binary result regarding match

** Output:** **{FALSE;2;3;FALSE;FALSE;2;FALSE;FALSE;2}**

**FREQUENCY(IF(C5:C13=G5,MATCH(B5:B13,B5:B13,0)),ROW(B5:B13)-ROW(B5)+1)**becomes**FREQUENCY({FALSE;2;3;FALSE;FALSE;2;FALSE;FALSE;2},ROW(B5:B13)-ROW(B5)+1)**

** Output: {0;3;1;0;0;0;0;0;0;0}**

**IF(FREQUENCY(IF(C5:C13=G5,MATCH(B5:B13,B5:B13,0)),ROW(B5:B13)-ROW(B5)+1),1)**becomes**IF({0;3;1;0;0;0;0;0;0;0},1)**

** Output: {FALSE;1;1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}**

**SUM(IF(FREQUENCY(IF(C5:C13=G5,MATCH(B5:B13,B5:B13,0)),ROW(B5:B13)-ROW(B5)+1),1))**yields to**SUM({FALSE;1;1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})**

** Output: 2**

- 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 (3 Methods)**

## Practice Section

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

## 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