0

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

Glenn
  • 15
  • 2
  • 6

2 Answers2

0

Where A contains targeted salespersons initials (can be dragged down), B contains range of all initials, and C contains range of all amounts:

=SUMIF($B$4:$B$8,$A1,$C$4:$C$8)
carter
  • 75
  • 9
  • I already have a formula to sum amounts by salesperson. What I need is a count of customers actually sold to, per salesperson. – Glenn Jan 11 '18 at 18:01
  • Click on "Image of my Excel file" above and you'll see what my file looks like (yellow highlight shows where formula is needed) – Glenn Jan 11 '18 at 18:07
  • Given what has been done for you so far, then you should now be able to achieve that "count of customers per salesperson". – Solar Mike Jan 12 '18 at 18:46
0

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • That works. Thank you for your time & expertise! But now I am curious how that formula works. Part of it is looking at the amount column? And it is returning a count? – Glenn Jan 11 '18 at 18:59
  • No problem, give me an hour and I'll add an explanation to my answer – barry houdini Jan 11 '18 at 19:09
  • What change is needed in the formula to make it work correctly when the data is filtered? – Glenn Jan 11 '18 at 20:24
  • You can use an additional condition with `SUBTOTAL` and `OFFSET`, see above (an alternative is to add a helper column to the data which would indicate which columns are filtered which would make the formula simpler – barry houdini Jan 11 '18 at 20:25
  • Yes, that works with data filtered. Thank you very much! – Glenn Jan 11 '18 at 20:50
  • Is there a way for me to message you directly? I wanted to ask how to modify this formula so it works when the data is filtered...=SUMPRODUCT((B$21:B$179=B7)/(COUNTIFS(D$21:D$179,D$21:D$179,B$21:B$179,B7)+(B$21:B$179<>B7))) – Glenn Jan 11 '18 at 22:29
  • You should be able to use a version of the FREQUENCY formula again - try this `=SUM(IF(FREQUENCY(IF(B$21:B$179=B7,IF(SUBTOTAL(3,OFFSET(B$21:B$179,ROW(B$21:B$179)-ROW(B$21),0,1)),MATCH(D$21:D$179,D$21:D$179,0))),ROW(D$21:D$179)-ROW(D$21)+1),1))` – barry houdini Jan 11 '18 at 23:22
  • Yes that worked. Thank you!!! I was hoping you would provide the FREQUENCY formula for me, because I read that it is more efficient as the data increases in size. It did not work initially, but after I typed the code in by hand, Excel picked up all the references and was accepted by Excel. – Glenn Jan 12 '18 at 15:44