-1

See image below. I use the following formula to count the number of unique customers on the total row of the table:

=SUMPRODUCT( -- (FREQUENCY(MATCH(C14:C20,C14:C20,0),ROW(C14:C20)-ROW(C14)+1)>0))

But I need a slightly different formula to count unique customers by salesperson for my summary section outside of the table.

Image: Excel example table and summary section - need formula in yellow highlighted cells

enter image description here

Glenn
  • 15
  • 2
  • 6

1 Answers1

0

Try this in C8,

=SUMPRODUCT((B$14:B$20=$B8)/(COUNTIFS(C$14:C$20, C$14:C$20, B$14:B$20, $B8)+(B$14:B$20<>$B8)))

Fill down.

  • That formula works. Thank you very much for your time. – Glenn Jan 08 '18 at 21:08
  • Note my recent edit that should allow you to fill right for unique count of sales orders. –  Jan 08 '18 at 21:09
  • Jeeped - filling right that returns a zero. Something is wrong. – Glenn Jan 08 '18 at 21:17
  • Your blank cells make the formula much more complex. If you get rid of the blanks and make all **B** into **$B** then it will work; otherwise it is a new question since additional criteria to accommodate blanks is required. –  Jan 08 '18 at 21:23
  • Jeeped - what modification in the formula would make it work when the data is filtered? – Glenn Jan 11 '18 at 20:29