Counting Multi-Select Column Criteria Formula

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

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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:

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • Kal
    Kal ✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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"

  • Kal
    Kal ✭✭

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


    Source Sheet of Multi-Select:

    Radis Status Column:


  • Genevieve P.
    Genevieve P. Employee Admin

    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"))

  • Kal
    Kal ✭✭

    Workstream is Single Dropdown Select:


    Formula Screenshot: Result is 0


    Cross Reference Screenshot:


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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:

    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

  • Kal
    Kal ✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!