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

Get FREE Advanced Excel Exercises with Solutions!

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.

Count Unique Values in Multiple Columns


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


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.
=SUMPRODUCT((1/COUNTIFS(B5:B13,B5:B13,C5:C13,C5:C13)))

  • At last, press the 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: 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.
=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 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.

Count Unique Values in Multiple Columns pivot


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


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo