Count If without counting duplicates?

**excelnoobz93** · 07-05-2013, 01:57 PM

Hello,

I need a bit of help. I need a formula/function for this, no VBA please.

I have column D with my sales agents, and then another column B with their clients. This list shows multiple transactions with their same clients but I only want to count one occurrence of a client, not duplicates. So I want to count how many clients they have even though, they may have the same client showing multiple times. I need a formula only please.

Example:

Client Sales Agent
Mark Alex
Sam Alex
Kim Kate
Mark Alex
Brian Kate

In this case the formula should return 2 for Alex, and 2 for Kate.

I use Excel 2007. Thanks!

**Ron Coderre** · 07-05-2013, 02:09 PM

With your sample data in A1:B6
and
Col_D containing Sales Agent names
D1: Alex
D2: Kate

This regular formula returns the count of unique client names for that Sales Agent

Formula:

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

**excelnoobz93** · 07-05-2013, 02:23 PM

Thanks for your help with this! I'm sorry I am a bit confused...let me try to clarify my situation:

Column A is empty. Column B starting in cell B2 down to about B1000 has a list of all client names including duplicates if there were multiple transactions. (I have many more client names than in my example)
Column D2 down to about D1000 has the list of the sales agent names. (I have more than just Alex and Kate, I have about 20+ different agents)

Does this clarify it better?

**Ron Coderre** · 07-05-2013, 02:38 PM

Since many people post examples that don't quite match their actual scenario, I posted a generic example that I thought you might be able to tweak to suit your situation.

Now that we know what you're actually using...
With
D2:D1000 containing Sales Agent names
and
B2:B1000 containing Client names
AND
F1:F20 containing the names of each Sales Agent

This regular formula begins the list of unique client counts per sales agent

Formula:

Please Login or Register  to view this content.

Copy that formula down through G20.

Does that help?

**Tony Valko** · 07-05-2013, 02:54 PM

One way...

If this is your data in the range A2:B6...

Mark......Alex
Sam......Alex
Kim.......Kate
Mark.....Alex
Brian.....Kate

List the unique agent names in a range of cells:

D2= Alex
D3 = Kate

Then, enter this array formula** in E2 and copy down:

=SUM(IF(FREQUENCY(IF(B$2:B$6=D2,MATCH(A$2:A$6,A$2:A$6,0)),ROW(A$2:A$6)-ROW(A$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You can easily generate the list of unique agent names using advanced filter:

http://contextures.com/xladvfilter01.html#FilterUR