COUNTIFS with a multiple drop down field

Options
Jen S.
Jen S. ✭✭
edited 10/25/22 in Formulas and Functions

I'm struggling with some syntax. I have the following fields:

I want to count status by team lead. The team lead is a dropdown allowing multiple entries, status is a dropdown with single entry. I found that it counts Joe, but can't find Jane.

I have two references to this table - {Retail - Status} and {Retail - Lead)

My original syntax s as follows:

=COUNTIFS({Retail - Status}, "Red", {Retail - Lead}, "Jane Jones") – Picks up Joe, but will not pick up Jane

Tried some variations:

=COUNTIFS (CONTAINS ("Green", {Retail – Status}, "TRUE", "FALSE"), CONTAINS ("Jane Jones", {Retail – Lead}, "TRUE", "FALSE").

=COUNTIFS(FIND("Jenny O’Rourke", {Retail-Lead}) > 0, {Retail-Status}, "Green")


Any assistance is appreciated

Best Answer

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    @Jen S.

    Hi Jen, you'll need to use the HAS function on multiselect dropdowns and multiselect Contacts columns:

    =COUNTIFS({Retail - Lead}, HAS(@cell, "Jane Jones"), {Retail - Status}, "Red")

    Please let me know if that works for you!

    -Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    @Jen S.

    Hi Jen, you'll need to use the HAS function on multiselect dropdowns and multiselect Contacts columns:

    =COUNTIFS({Retail - Lead}, HAS(@cell, "Jane Jones"), {Retail - Status}, "Red")

    Please let me know if that works for you!

    -Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Jen S.
    Jen S. ✭✭
    Options

    Thank you, that worked

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @Jen S. Whoop! Glad it did :) - Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Jen S.
    Jen S. ✭✭
    Options

    @Ryan Sides So, I spoke too soon on this. I'm getting weird errors. The first query below is a summary sheet field. The second query is where I'm trying to pull the same info into a summary sheet since I have 7 names with 4 different statuses which creates 112 different combinations.

    I would really rather not put 112 summary fields in to capture this information.

    Summary query:

    =COUNTIFS([Team Lead]:[Team Lead], "Andy Becker", Status:Status, "Gray")

     Separate Sheet Query with the cross references

    =COUNTIFS({Retail - Lead}, HAS(@cell, "Andy Becker"), {Retail - Status}, HAS(@cell,"Gray"))

     

    The first query returns 10 which is correct, the second query returns 14. In fact every query (different names, statuses) are all returning 14.  

  • Genevieve P.
    Options

    Hi @Jen S.

    I would recommend going the Summary Sheet route with this!

    You could then have a Contact Column with a single contact listed down the column in each cell. Then you can reference this cell in your formula instead of writing out the name:

    HAS(@cell, "Andy Becker") - changes to - HAS(@cell, [Contact Column]@row)


    Then for the Status column you don't need the HAS function, as it will only ever have one selection at a time.

    Try this:

    =COUNTIFS({Retail - Lead}, HAS(@cell, [Contact Column]@row), {Retail - Status}, "Gray")

    Then in a second column, you could count the other status colours:

    =COUNTIFS({Retail - Lead}, HAS(@cell, [Contact Column]@row), {Retail - Status}, "Green")



    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    edited 11/08/22
    Options

    @Jen S. Hi Jen, did you get this one figured out? Sorry for my delay in replying I was out last week.

    @Genevieve P. is spot on in her reply!

    -Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!