Count and distinct count in excel

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?
LVL 2
coronoahcoroAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
jppintoCommented:
Your best option is to use a Pivot Table to work on the data...
0
 
coronoahcoroAuthor Commented:
Why do you divide the number 1 with the count?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
coronoahcoroAuthor Commented:
I think I understand now, but how can I get the rep count? I attached the excel file here

 test.xlsx
0
 
Patrick MatthewsCommented:
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
 
coronoahcoroAuthor Commented:
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
 
coronoahcoroAuthor Commented:
nvm I can use this formula:

=COUNTIF($A$2:$A$635, A2)
0
 
Patrick MatthewsCommented:
I already told you how to do that within the PivotTable in my first comment, http:#a36569747
0
 
coronoahcoroAuthor Commented:
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
 
coronoahcoroAuthor Commented:
sorry I didn't know that PT can do count to. I think I'm ok now
 test.xls
0
 
Patrick MatthewsCommented:
Yes, that last file was exactly what I had in mind :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.