# Count only unique accounts based on criteria

I am working on a report and need help.
I have a report that is a customer list of water and sewer customers.  Each Customer has a unique customer number.  However, some customers have more than one account (they may have one for their home water, another for irrigation, another for an outside shop etc...) but still use the same customer number.

Each customer is also located in a "zone."

I would like to find a formulate that could tell me the number of CUSTOMERS (not accounts) in each zone on the report.

I have a formula that will count only the unique customer numbers.  However, if you can provide a better one that i can also add on to in order to lookup by zone, that would be great.

The formula I have is {=sum(if(frequency(sheet1!k2:sheet1!k318,Sheet 1!k2:Sheet1!k318)>0,1))}

(Pay no attention to the sheet and cell references...I am just using them as an example.
Sample-to-Experts-Exchange.xlsx
###### Who is Participating?

x

Commented:
Assuming you list the unique zones, say in H2:H4, then in say I2:

=SUMPRODUCT((\$A\$2:\$A\$16=H2)/COUNTIF(\$B\$2:\$B\$16,\$B\$2:\$B\$16&""))

copied down
0

Budget Analyst / ProjectsAuthor Commented:
I think that is going to work wonderfully!  Thanks so much for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.