Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Count only unique accounts based on criteria

Posted on 2013-01-15
2
Medium Priority
?
293 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

610 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