[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Count and distinct count in excel

Posted on 2011-09-20
11
Medium Priority
?
291 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
[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
  • 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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 93

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 93

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 93

Expert Comment

by:Patrick Matthews
ID: 36577348
Yes, that last file was exactly what I had in mind :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

649 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