Checking a box if multiple criteria are met.
Needing a little help, I am needing a cell to have its box checked marked depending on certain criteria. I have the basics but the road block I'm running into is the last criteria and I know I'm missing something.
I am needing the blue box to have it's box checked. I have it linked to another sheet that this sheet is drawing its data from. For the box to be check I need to have a form submitted for location "Administrative Campus" for the Emergency Procedure "Evacuation Procedure" and have the procedure reviewed with 1st, 2nd, 3rd, Weekend 1st, Weekend 2nd, and Weekend 3rd (which is answered by multiselect on the other form) and all goes into the same cell on the other form.
My current algorithm works for the check boxes seen below, but for the second I need an additional layer of countifs and I'm stuck=
=IF(COUNTIFS({Emergency
Procedure Review Data Sheet Range 3}, @cell = "Administrative
Campus", {Emergency Procedure Review Data Sheet Range 2}, @cell =
"Evacuation Procedures") > 0, 1, 0)
My current formula I'm needing for highlighted cell that isn't working.
IF(COUNTIFS({Emergency
Procedure Review Data Sheet Range 3}, @cell = "Administrative
Campus", {Emergency Procedure Review Data Sheet Range 2}, @cell =
"Evacuation Procedures", {Emergency Procedure Review Data Sheet Range
1}, @cell = "1st, 2nd, 3rd, Weekend 1st, Weekend 2nd, Weekend 3rd") > 0, 1, 0)
Any help is appreciated!
Answers
-
Are you looking for the multi-select to have all of those options selected or just at least one of the options selected?
-
I'm looking to for the multi-select to have all of those options selected.
-
In that case you would need to use an AND function to string together some HAS functions.
=COUNTIFS({Multi-Select Column}, AND(HAS(@cell, "1st"), HAS(@cell, 2nd"), HAS(@cell, "3rd")))
-
Paul,
I am not having success placing the And(Has function, I'm not for sure if I'm placing it in the incorrect place in the formula but I'm receiving either paraseable or incorrect argument.
Here's what I have
=IF(COUNTIFS({Emergency Procedure Review Data Sheet Range 3}, @cell = "Administrative Campus", {Emergency Procedure Review Data Sheet Range 2}, @cell = "Evacuation Procedures", AND(HAS({Emergency Procedure Review Data Sheet Range 1}, @cell, "1st"), HAS(@cell, "2nd"), Has(@cell, "3rd) > 0, 1, 0)))
There's more after that but figured if I got those three to work, the others would just be repeated for those.
-
You have the range mixed up. The range does not go inside of the AND function. Take another look at hte example I provided to see the proper syntax.
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!