Counting Multi-Select Column Criteria Formula
![Kal](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
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 -
I Tried Both Recommended Formulas but Still Receive 0 as the Result.
-
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 -
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:
-
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 -
Workstream is Single Dropdown Select:
Formula Screenshot: Result is 0
Cross Reference Screenshot:
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Yes, I See That. I Have Corrected The Reference and Now I Have The Correct Results. Thanks
-
Wonderful! I'm glad we could get there in the end 😊
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!