Counting Multi-Select Column Criteria Formula

Options
Kal
Kal ✭✭
in Formulas and Functions

God Day Smartsheet Community:

I am Trying Count My Risk in my RAID Log. However The RAID ID is a Multi-Select Dropdown. My Formula is Returning 0 Risk when there should be at least 1 Based on the Rest of The Formula

There is 1 Risk in my Source Sheet that Meets The Criteria of CPC, Open RAID Status, and Contains R in The RAID ID Column which is The Multi-Dropdown Column

=COUNTIFS({Work-Stream}, OR(@cell = "CPC", @cell = "EPM-MDM"), {RAID Status}, "Open", {RAID ID}, CONTAINS("R", @cell))

Thanks

· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Kal

    Thank you for these screen captures! Based on this, it looks like you may have the wrong column selected as the {cross sheet reference} for your Work-Stream reference:

    =COUNTIFS({Work-Stream}, "CPC", {RAID Status}, "Open", {RAID ID}, HAS(@cell, "R")) + COUNTIFS({Work-Stream}, "EPM-MDM", {RAID Status}, "Open", {RAID ID}, HAS(@cell, "R"))

    In your screen capture, I see that the Status column is highlighted in blue, instead of the Workstream column:

    Screenshot 2022-11-02 at 14.56.23.png

    Try Editing that {reference} and make sure that you click on the Work-Stream column name to highlight that entire column instead, then Update the reference. This will update all formulas in your sheet to look to the correct place!

    Let me know if that works for you.

    Cheers!

    Genevieve

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

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee
    Options

    Hi @Kal

    I would suggest using the HAS Function if you're looking in a multi-select column - it checks to see if the cell has that specific selection among others:

    =COUNTIFS({Work-Stream}, "CPC", {RAID Status}, "Open", {RAID ID}, HAS(@cell, "R"))

    Then it looks like you have another Work Stream you want to filter by. You can do this by adding 2 COUNTIFS Functions together:

    =COUNTIFS({Work-Stream}, "CPC", {RAID Status}, "Open", {RAID ID}, HAS(@cell, "R")) + COUNTIFS({Work-Stream}, "EPM-MDM", {RAID Status}, "Open", {RAID ID}, HAS(@cell, "R"))

    Cheers,

    Genevieve

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

    · Share on FacebookShare on Twitter
  • Kal
    Kal ✭✭
    Options
    https://community.smartsheet.com/discussion/comment/349103#Comment_349103

    I Tried Both Recommended Formulas but Still Receive 0 as the Result.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Options

    Hi @Kal

    Can you post a screen capture of the source sheet (but block out sensitive data)?

    This formula is looking for the exact letter "R" selected as one option in the multi-select, for example if the cell has "R", "S", and "T" all selected as individual letters. Is that what your multi-select column looks like?

    Is it possible that some of the other values aren't exactly typed how they are in your source sheet? E.g. "Open" versus "Opened"

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

    · Share on FacebookShare on Twitter
  • Kal
    Kal ✭✭
    Options

    I Doubled Checked to Make Sure there not Any Extra Spaces in My List and there none.


    Source Sheet of Multi-Select:

    image.png

    Radis Status Column:

    image.png


    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Options

    Hi @Kal

    Thank you for this information! This confirms that two references and criteria are correctly built. How about the first range/reference? Is the Work-Stream column a primary column, where the value is a single text value?

    Can you show me what your formula looks like in the sheet open, like so:

    =COUNTIFS({Work-Stream}, "CPC", {RAID Status}, "Open", {RAID ID}, HAS(@cell, "R")) + COUNTIFS({Work-Stream}, "EPM-MDM", {RAID Status}, "Open", {RAID ID}, HAS(@cell, "R"))

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

    · Share on FacebookShare on Twitter
  • Kal
    Kal ✭✭
    Options

    Workstream is Single Dropdown Select:

    image.png


    Formula Screenshot: Result is 0

    image.png


    Cross Reference Screenshot:

    image.png


    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Kal

    Thank you for these screen captures! Based on this, it looks like you may have the wrong column selected as the {cross sheet reference} for your Work-Stream reference:

    =COUNTIFS({Work-Stream}, "CPC", {RAID Status}, "Open", {RAID ID}, HAS(@cell, "R")) + COUNTIFS({Work-Stream}, "EPM-MDM", {RAID Status}, "Open", {RAID ID}, HAS(@cell, "R"))

    In your screen capture, I see that the Status column is highlighted in blue, instead of the Workstream column:

    Screenshot 2022-11-02 at 14.56.23.png

    Try Editing that {reference} and make sure that you click on the Work-Stream column name to highlight that entire column instead, then Update the reference. This will update all formulas in your sheet to look to the correct place!

    Let me know if that works for you.

    Cheers!

    Genevieve

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

    · Share on FacebookShare on Twitter
  • Kal
    Kal ✭✭
    Options
    https://community.smartsheet.com/discussion/comment/349604#Comment_349604

    Yes, I See That. I Have Corrected The Reference and Now I Have The Correct Results. Thanks

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Options

    Wonderful! I'm glad we could get there in the end 😊

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!