Checking a box if multiple criteria are met.

brent.kendall

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!

Paul Newcome

Are you looking for the multi-select to have all of those options selected or just at least one of the options selected?

brent.kendall

I'm looking to for the multi-select to have all of those options selected.

Paul Newcome

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

brent.kendall

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.

Paul Newcome

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.