Excel tutorials

Count Distinct Values - Count Unique Values

I wrote about duplicates, distinct or unique data in the tutorials at the links below.

In this tutorial, I will show how we can count distinct values and unique data in two separate columns if the condition we set as a criterion matches. When working with data in Excel, we have a situation where certain data is often repeated. However, when you need to count distinct data or unique data only or values or numbers for a particular criterion, especially if the values are in two separate columns, then we need more formulas to solve the problem. If you are wondering how to count unique data, check out this tutorial with colorful examples and notice Excel formulas.

When it comes to distinct data and only unique values, the following should be kept in mind:

  • Distinct values are those data that appear more than once
  • Unique values are those data that appear only once and have no duplicates

We need two results, ie we want to count the following:

  1. What is the total number of restaurants (distinct data) for a given city (as a condition) but we want to ignore all the repeating ones?
  2. How many unique restaurants in a particular city, ie. We want to count only their names but do not repeating?

1. How to Count Distinct Values in Two Separate Columns

In the image below, notice the situation on an Excel worksheet. Column 'A' lists the City names. The two separate columns 'B' and 'D' contain the restaurant names. Columns 'C' and 'E' contain information that is irrelevant to this task.

Thus, in the picture below note that in Boston (the condition in the cell 'G2') is a total of four restaurants (McDonalds, Burger King, McDonalds and Popeyes) and the expected results in cell 'H2'. Also notice that the McDonalds restaurant appears twice. So that the expected result in the cell 'I2' to set a condition in the cell 'G2', should be three restaurants (McDonalds, Burger King and Popeyes).

The same situation is true for the set condition in cell 'G3'. Note that the total number of Los Angeles conditional restaurants is the sum of all restaurants, regardless of their name, hence five restaurants (Wendys, McDonalds, Pizza Hut, Pizza Hut and Wendys). But when we count distinct restaurants, ie only restaurant names, the result is number three (Wendys, McDonalds, Pizza Hut).

Counting distinct data or values in Excel

 

To complete this task and count restaurants for the condition, we need to add helper columns. These "extra" columns help us to separate the "City-Restaurant" pairs in the two adjacent columns, so it will be easier for us to do the counting by default (see image below).

In the 'K' column, we will single out all the cities that contain the name of the restaurant in pairs (based on the original range 'A2:D6').

The formula in the 'K2' cell is as follows (copy down):
=IFERROR(IFERROR(INDEX($A$2:$B$6;ROW($K2)-ROW($K$1);COLUMN($A$2:$A$6));INDEX($A$2:$D$6;ROW($K2)-ROW($K$1)-ROWS($A$2:$D$6);COLUMN($A$2:$A$6)));"")

In the 'L' column we will extract, in the order all the restaurants that will belong to a particular city (based on the original columns 'B2:B6' and 'D2:D6').

The formula in the 'L2' cell is as follows (copy down):
=IF(IFERROR(INDEX($B$2:$B$6;ROWS(C1:$C$1));IFERROR(INDEX($D$2:$D$6;ROWS(C1:$C$1)-ROWS($B$2:$B$6));""))=0;"";IFERROR(INDEX($B$2:$B$6;ROWS(C1:$C$1));IFERROR(INDEX($D$2:$D$6;ROWS(C1:$C$1)-ROWS($B$2:$B$6));"")))

Counting distinct values or data

 

Now that we have set up the auxiliary columns we are going to create a formula for counting distinct values.

To count the total number of restaurants in a particular city, place the formula below in the 'H2' cell. (copy down)
=COUNTIFS($A$2:$A$6;G2;$B$2:$B$6;"<>"&"")+COUNTIFS($A$2:$A$6;G2;$D$2:$D$6;"<>"&"")

To count the distinct name of a restaurant in a particular city, place ARRAY or 'CSE formula' in cell 'I2' below (copy down): You end this formula with CTRL+SHIFT+ENTER, not just the Enter key.
=COUNT(1/FREQUENCY(IF($K$1:$K$12=$G2;IF($L$1:$L$12<>"";MATCH($L$1:$L$12;$L$1:$L$12;0)));ROW($L$1:$L$12)-ROW($L$1)+1))

2. How to Count Unique Values in Two Separate Columns

In the situation for the same task but when we want to count the unique values then we work in the same way, ie. again we can use helper columns. So, notice in the picture below the different expected results. Remember, I said at the beginning of this tutorial, that unique data only appears once and it doesn't repeat itself.

We have four restaurants in the city of Boston but the McDonalds restaurant appears twice. So, we want to ignore it. Furthermore, it follows that we are left with two restaurant names (Burger King and Popeyes).
Also for the condition set Los Angeles city our expected result is number 1. Notice in the picture below that only McDonalds restaurant does not repeat (ie only appears once)

Counting unique data or values from the range of allocated

 

If we want to Count Unique data in two separate columns for a given condition (criteria) then the calculation is similar to the above actions.

Count unique data in two separate columns using auxiliary columns

 

So everything is similar to the previous example above. We have auxiliary columns and the ARRAY or 'CSE formula' in the 'I2' cell is as follows (copy down): You end this formula with CTRL+SHIFT+ENTER, not just the Enter key.
=SUM(--(FREQUENCY(IF(($K$2:$K$12=G2)*($L$2:$L$12<>"");MATCH("~"&$L$2:$L$12;$L$2:$L$12&"";0));ROW($L$2:$L$12)-ROW($L$2)+1)=1))

With this formula we've counted only unique data is not repeated. If you want to see the flow of calculations or the results of a particular nested function within a formula, then visit the Evaluate Formula tutorial and use the 'F9' key.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.