# 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
###### Who is Participating?

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))

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

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

Commented:
Your best option is to use a Pivot Table to work on the data...
0

Author Commented:
Why do you divide the number 1 with the count?
0

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

test.xlsx
0

Commented:
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

Author 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

Author Commented:
nvm I can use this formula:

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

Commented:
I already told you how to do that within the PivotTable in my first comment, http:#a36569747
0

Author 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

Author Commented:
sorry I didn't know that PT can do count to. I think I'm ok now
test.xls
0

Commented:
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.