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

Count Unique Values in Multiple Columns


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)

Count Unique Values in Multiple Columns IF function

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

Count Unique Values in Multiple Columns IF

  • Now, click on the blank cell in the Total Unique column and type the following formula.
=SUM(G5:G13)

  • Finally, press the ENTER key.

Count Unique Values in Multiple Columns if combo

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.

Count Unique Values in Multiple Columns SUMPRODUCT

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"),)&""

Count Unique Values in Multiple Columns INDIRECT

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

Count Unique Values in Multiple Columns INDIRECT

That’s it.

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


Similar Readings:


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.

Count Unique Values in Multiple Columns pivot table

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

Count Unique Values in Multiple columns pivot table

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

Count Unique Values in Multiple Columns pivot

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.

Count Unique Values in Multiple Columns conditional formatt

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

Count Unique Values in Multiple Columns data validation

  • 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))

Count Unique Values in Multiple Columns combination function

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

Count Unique Values in Multiple Columns

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


Related Articles

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo