Unique Count in Summary


How do you write a formula that takes the UNIQUE count of values in the summary. For example: Product A: Rep 1 and Rep 2, Total = 2 Product B: Rep 2 and Rep 3, Total = 2 Product C: Rep 4, Total = 1 At the summary level for all products, the count of reps would be 5 however, the count of unique reps is 4. How can you get the unique count of reps at the summary level?

Best Answer

  • nathan_rudman
    Oleg, if you have an "easy" solution I'm curious because to my knowledge there is no "count" function.

    Victoria, if you want only unique associations to be created, use numbered lists. To count, you have to use some tricks, unless Oleg can light our way.


  • Hello,

    If I may say, your question is unclear, very much so.
  • I don't believe it's unclear at all. I'm asking for a unique count in the summary items. 

    If Bob and Joe sell apples,
    and Bob and Chris sell oranges,
    at the summary item which would be fruit,
    what is the unique count of reps selling fruit? 
    The answer would be 3. In today's world, the summary count of reps selling fruit is 4
  • ok let's ask precisely then:
    - what do you call summary ?
    - what are fruits ? hierarchy, line items ?
    - what are the reps ? hierarchy, line items ?
    - what's the relationship between those two ?(technically) ?
  • Victoria, Seems it's very easy case
    Have you solved it?
  • No I have not solved it. If you can figure out a way then that would be helpful
  • Fruits and reps are both in hierarchies. The relationship is that fruits are assigned to reps to sell. 
  • Count1 is my first module, with Products and Reps lists, and one Line Item (Sell).
    Count2 is my second, with only the Reps lists and one Line Item (Count).
  • Victoria, Eric's response above is certainly the best solution to your problem.
    Notice that the calculation used in the second line item makes sure that a rep will only be counted if they have some product assigned to them to sell, so this should solve your problem perfectly.