Solved

Count and distinct count in excel

Posted on 2011-09-20
11
278 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 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