How do I count the number of unique customers sold to, per sales person?

Question

To get the number of customers sold to by salesperson, my guess is I could either count the number of sales orders per salesperson per unique customer (i.e. not counting more than the first sale per customer)...

Or, count the number of unique customers per salesperson, where at least one sale is present.

I have done some research but I am still not sure which formula to use and/or how to write it. Here are some examples of what I found.

Excel sumproduct with countifs

count-unique-values-in excel-with-a-contition

count-unique-values-in-excel-with-two-conditions

excel-forumla-countifs-multiple-criteria-distinct-count

Image of my Excel File

Link to my Excel Example File

barry houdini barry houdini · Accepted Answer · 2018-01-11T18:43:29

The most efficient formula AFAIK is with FREQUENCY function, similar to my formula suggestion in your third link "count unique values in excel with 2 conditions" i.e. this formula in D10

=SUM(IF(FREQUENCY(IF(B$16:B$21=B10,IF(E$16:E$21>0,MATCH(C$16:C$21,C$16:C$21,0))),ROW(C$16:C$21)-ROW(C$16)+1),1))

confirmed with CTRL+SHIFT+ENTER and copied down to D11

If you want it to work with filtered data try this version

=SUM(IF(FREQUENCY(IF(B$16:B$21=B10,IF(SUBTOTAL(9,OFFSET(E$16:E$21,ROW(E$16:E$21)-ROW(E$16),0,1)),MATCH(C$16:C$21,C$16:C$21,0))),ROW(C$16:C$21)-ROW(C$16)+1),1))

Explanation:

The MATCH function is the crucial part, that will return the same relative row number for repeated values. For your data MATCH function returns the following array:

{1;2;3;3;5;6}

Notice that the repeated 3 corresponds to your repeated customer Smith The internal IF function returns the MATCH values only for rows where B10 matches (i.e. correct salesperson) and column E > 0 (there's a sale) so for your data the above array becomes this:

{1;2;3;3;FALSE;FALSE}

The first four values are the same as above because those 4 rows match salesman and have sales value > 0, rows 5 and 6 are FALSE because one or both conditions is FALSE

So, for our unique count we need to count the number of different numbers in that array (3)

FREQUENCY does that by assigning that “data array” to the “bins array” returned by

ROW(C$16:C$21)-ROW(C$16)+1

…. which evaluates to the following: {1;2;3;4;5;6}

So when the above data array is distributed into the bins (see FREQUENCY function for help on how this happens) you get this array, finally from FREQUENCY

{1;1;2;0;0;0;0}

[bin 1 gets 1 number, bin 2 gets 1 number but bin 3 gets 2 (the 2 threes)] Now the external IF function assigns 1 to every non-zero value in that array, and SUM sums those 1s so the result is 3

How do I count the number of unique customers sold to, per sales person?

2 Answers