?
Solved

Count and distinct count in excel

Posted on 2011-09-20
11
Medium Priority
?
286 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
Industry Leaders: 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!

 
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

Independent Software Vendors: 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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 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