Counting unique values in Excel

---

Recently, I needed to get a count of unique values in a data set but I noticed that the task couldn’t be done using a simple Count statement due to the dependency of the unique records which I wanted to count to another field. Consider the data set below:

In the first column we have unique Order IDs but columns B and C do not necessarily contain unique records. Each “City” may have multiple “Customer IDs” associated to it and each unique customer ID may have multiple Order IDs attached to it (i.e. for those customers who have placed multiple orders).

​For each City, I want to count the number of unique Customer IDs. For example, the number of unique Customer IDs for Toronto should be equal to 3. In order to do this, I first listed all of the Cities in Column E. This could be done by going to the Date tab in the ribbon and removing duplicates.

Here is the formula I used in order to count the number of unique customer IDs per City

{=SUM(IF($B$2:$B$11=E2,1/COUNTIFS($C$2:$C$11,$C$2:$C$11,$B$2:$B$11,E2)))}
 
As this is an array formula make sure to do a “Ctrl+Shift+Enter” on the keyboard. Here is the outcome when I apply the formula to the entire range.

---