Counting Multi-Select Column Criteria Formula

Kal · 10/27/22

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

Genevieve P. · 11/02/22

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

Genevieve P. · 10/28/22

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 · 10/28/22

https://community.smartsheet.com/discussion/comment/349103#Comment_349103

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

Genevieve P. · 10/28/22

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 · 10/28/22

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. · 10/31/22

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 · 10/31/22

Workstream is Single Dropdown Select:

Formula Screenshot: Result is 0

Cross Reference Screenshot:

Genevieve P. · 11/02/22

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 · 11/03/22

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

Genevieve P. · 11/03/22

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

Counting Multi-Select Column Criteria Formula

Best Answer

Answers

Help Article Resources

Categories