Get number of distinct items in list with formula - excluding blanks

Hi experts,

I'm trying to get the count of distinct orders per sales rep in the attached file. That formula should work with or without blanks in the sales rep column.

As the table on the Report tab changes size, I'd like to refer to it by table ids rather than by ranges.

Thanks for your help,

Thomas
Copy-of-Daily-Sales-Today--6-.xlsx
LVL 39
nutschAsked:
Who is Participating?
 
NBVCCommented:
Try:

=SUMPRODUCT((Sales_Line[Salesperson]=B7)/COUNTIF(Sales_Line[SO No.],Sales_Line[SO No.]&""))
0
 
nutschAuthor Commented:
Perfect, thanks.

Thomas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.