Need to Count Distinct Based on Multiple Criteria

Hi there!


I have a database (Sheet 1) where I log all my company's gifting to social media influencers and the subsequent exposure on Social Media in one sheet. I organize them by the Name of Influencer, the month we gifted, the month they posted (if they did, otherwise its blank), among others.

In a different sheet (Sheet 2), I am trying to count distinct the Name of the influencer we gifted from (Sheet 1). But I want it to be interactive so within Sheet 2, I have a multiple dropdown cell I will list all the months I am interested in evaluating. For example, if I wanted to see the distinct count of names that we gifted in January, February, and March I would select those three months in that dropdown menu.


I am able to to the distinct count without any problems when it is just one month to evaluate but when it becomes multiple values, the formula below does not work.

=COUNT(DISTINCT(COLLECT({DD Influencer Database Range 1}, {DD Influencer Database Range 2}, Joined@row)))


Any solutions to this?

Thank you!

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Amt501 

    I fixed as you need by using multiselect option, please check it:

    =IFERROR(COUNT(DISTINCT(COLLECT({Name}, {Gifts Sent}, OR(@cell = Joined4, @cell = Joined5,
    @cell = Joined6, @cell = Joined7, @cell = Joined8, @cell = Joined9, @cell = Joined10,
    @cell = Joined11, @cell = Joined12, @cell = Joined13, @cell = Joined14, @cell = Joined15)))), "")
    

    the following screenshot shows the result


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!