Solved

Count only unique accounts based on criteria

Posted on 2013-01-15
2
286 Views
Last Modified: 2013-01-15
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
0
Comment
Question by:Johnette Connelley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 38779756
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
 

Author Closing Comment

by:Johnette Connelley
ID: 38779848
I think that is going to work wonderfully!  Thanks so much for your help!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question