## 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
Solved

# Count and distinct count in excel

Posted on 2011-09-20
280 Views
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
Question by:coronoahcoro
• 6
• 4

LVL 33

Expert Comment

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

LVL 92

Accepted Solution

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

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

LVL 2

Author Comment

ID: 36573312
Why do you divide the number 1 with the count?
0

LVL 2

Author Comment

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

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

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

ID: 36574360
nvm I can use this formula:

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

LVL 92

Expert Comment

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

LVL 2

Author Comment

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

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

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

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…