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

  • Paul Newcome
    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?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • Paul Newcome
    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")))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!