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
Solved

Count and distinct count in excel

Posted on 2011-09-20
11
280 Views
Last Modified: 2012-05-12
I have an excel file with these columns:

Rep #    Cust #     Amount

In the excel files there are multile records of the rep # and cust #. What I would like to do is to group the same rep # together and count how many of those rep # occurs then after that I would like to know how many distinct customer for each rep.  How can I do this?
0
Comment
Question by:coronoahcoro
  • 6
  • 4
11 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 36569663
Your best option is to use a Pivot Table to work on the data...
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36569747
I'd definitely use a PivotTable.

1) Add a "helper" column to your source data to help calculate the distinct customer counts.  In Excel 2007 or later:

=1/COUNTIFS($A$2:$A$24,A2,$B$2:$B$24,B2)

For Excel 2003 or earlier:

=1/SUMPRODUCT(($A$2:$A$24=A2)*($B$2:$B$24=B2))

Adjust ranges as needed

2) Create your PT, and use Rep as row label, Count of Rep as one data field, and Sum of <Helper Column Name> for your distinct customer counts

You may also be interested in this article on how to make your PivotTables more dynamic:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
0
 
LVL 2

Author Comment

by:coronoahcoro
ID: 36573312
Why do you divide the number 1 with the count?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 2

Author Comment

by:coronoahcoro
ID: 36573446
I think I understand now, but how can I get the rep count? I attached the excel file here

 test.xlsx
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36573932
If you mean, how can you get the count of distinct reps, then in a single cell enter this array formula:

{=SUM(1/COUNTIF(A2:A635,A2:A635))}

To enter an array formula, do not enter the curly braces, and hit Ctrl+Shift+Enter instead of Enter to finish it off.  Excel will then display those braces to indicate that it's an array formula.
0
 
LVL 2

Author Comment

by:coronoahcoro
ID: 36574353
Actually I just would like to count how many times each rep shows up in the report, like rep # 1045 shows up 9 times
0
 
LVL 2

Author Comment

by:coronoahcoro
ID: 36574360
nvm I can use this formula:

=COUNTIF($A$2:$A$635, A2)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36574644
I already told you how to do that within the PivotTable in my first comment, http:#a36569747
0
 
LVL 2

Author Comment

by:coronoahcoro
ID: 36574754
Yes, and I was looking at your post but sorry I don't understand what you meant by
' ... Count of Rep as one data field ... '

This is what I have. I am just wondering how can I put the count on column B to the pivot table per reps per row.
 test.xls
0
 
LVL 2

Author Comment

by:coronoahcoro
ID: 36574969
sorry I didn't know that PT can do count to. I think I'm ok now
 test.xls
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36577348
Yes, that last file was exactly what I had in mind :)
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

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

792 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