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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!