Count unique customers by sales person - Array?

[Teledog]

I have two columns that I'm trying to get a single count for. Column A has my sales people, column B has their customers. I can't quite figure out how to get just a count for each unique customer. For example, the last sales person has 3 sales, and two customers. I want to return a value of 2 for him.


I am using Excel 2010, no macros.

Thanks!

 

[Ron Coderre]

I can't see any posted data. Can your list have the same customer listed more than once for the same salesperson?

If no....Use a Pivot Table to get the count of customers by sales person:
• Select your 2-column list (top cells contain headings: Salesperson, Customer)
• Insert.Pivot_table.Pivot_table
...Select a destination for the Pivot table
...Drag Salesperson to the Row_Labels area
...Drag Customer to the Values area (Since they are not numeric...you'll get a count)

The pivot table should now display each salesperson and the count of customers.

Does that help?

 

[Aladin Akyurek]

I have two columns that I'm trying to get a single count for. Column A has my sales people, column B has their customers. I can't quite figure out how to get just a count for each unique customer. For example, the last sales person has 3 sales, and two customers. I want to return a value of 2 for him.


I am using Excel 2010, no macros.

Thanks!

Assuming that you want a unique count of customers per sales person...

A2:A5 houses sales people, B2:B25 customers, and E2 a sales person of interest.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$2:$B$25<>"",IF($A$2:$A$25=E2,
  MATCH("~"&$B$2:$B$25,$B$2:$B$25,0))),
   ROW($B$2:$B$25)-ROW($B$2)+1),1))

 

[Snakehips]

Aladin,

Can you please explain why you include the tilde in above formula.

Do you do it just as a matter of good practice to legislate for wildcards?

 

[Teledog]

Ron,

The Pivot Table counts all customers, not the unique ones. So this was not the fix but thank you for trying. I appreciate your effort.

Aladin,

You are amazing and I am very impressed. Thank you for your assistance. I could not for the life of me figure out how to make the nested array work. BTW there are no blanks in my table. I don't know if this changes the way your formula accounts for "".

 

[Aladin Akyurek]

...
Aladin,

You are amazing and I am very impressed. Thank you for your assistance. I could not for the life of me figure out how to make the nested array work.

You are welcome.

BTW there are no blanks in my table. I don't know if this changes the way your formula accounts for "".

If you can guarantee 'no blanks', the formula can be modified to:

=SUM(IF(FREQUENCY(IF($A$2:$A$25=E2,
  MATCH("~"&$B$2:$B$25,$B$2:$B$25&"",0)),
   ROW($B$2:$B$25)-ROW($B$2)+1),1))

You can even remove the "~"& and &"" bits if there are on special meaning chars surrounding the relevant entries:

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

 

[Aladin Akyurek]

Aladin,

Can you please explain why you include the tilde in above formula.

Do you do it just as a matter of good practice to legislate for wildcards?

The "~"& and &"" bits in:

=SUM(IF(FREQUENCY(IF($B$2:$B$25<>"",IF($A$2:$A$25=E2,
  MATCH("~"&$B$2:$B$25,$B$2:$B$25&"",0))),
   ROW($B$2:$B$25)-ROW($B$2)+1),1))

are there in case spcial meaning chars like < surround the relevant entries. They can be safely removed if such cannot happen.

 

[Ron Coderre]

A bit late on this one, but...
With
A1:A100 containing Salesperson names (A1 is a heading)(blanks allowed)
B1:B100 containing Customer Names (B1 is a heading)(blanks allowed)
and
C2: a Salesperson to find a Customer count for

This regular formula returns the count of unique customers for the C2 name

D2: =COUNT(INDEX(1/((MATCH(C2&"_"&B2:B100,A2:A100&"_"&B2:B100,0)
=(ROW(A2:A100)-ROW(A2)+1))),0))

Does that help?