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

Posted on 2012-09-12
Last Modified: 2012-09-12
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,

Question by:nutsch
    LVL 23

    Accepted Solution


    =SUMPRODUCT((Sales_Line[Salesperson]=B7)/COUNTIF(Sales_Line[SO No.],Sales_Line[SO No.]&""))
    LVL 39

    Author Closing Comment

    Perfect, thanks.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now