Formula to Find Unique Customers per City

Hello all,

I have a table of transactions. The table has columns for Customer, City and Revenue. A customer can have multiple transactions per city. A customer can also have transactions in multiple cities. I need a formula in a helper column to flag each unique customer per city. I know how to use a COUNTIF formula nested in an IF formula to flag unique customers in the total data set. What I can't figure out is how to count unique customers per city. See attached file for a very simplified version of the table and what I'm trying to accomplish. In the actual data set there are thousands of transactions with hundreds of customers and scores of cities.

I will be summarizing the data in a pivot table to count unique customer per city and total revenue per city. I'm assuming I'll need a helper column to count the unique customers per city, but if there is a way to do that in the pivot table then all the better.

So, as an example, XYZ Corp. might have two transaction in Houston for $1,000 each. XYZ Corp. might also have one transaction in San Francisco for $1,000. In the pivot table XYZ Corp. will count as 1 unique customer in Houston, their revenue will sum up as $2,000. XYZ Corp. will also count as 1 unique customer in San Francisco, their revenue for San Francisco will sum up as $1,000.

I appreciate whatever help someone can provide.

Unique Customer Count.xlsx

Here is what I do in similar situations...might be a fancier way, but it seems to work for me...in your example, in C2 I put

Please Login or Register  to view this content.

Then, if you make a pivot table of columns A to D, you can use Sum of Unique (Column C) and Sum of Revenue (Column D), with your row labels being City (Column B).

In the formula in C2, you can extend beyond row 1000 if needed to fit your data size.

Does that give you the results you are looking for?

That worked. I had to make one adjustment as I wasn't originally accounting for cities with the same name in different states. So, I added a third criteria to the formula for the State column. I appreciate your help.