Check a checkbox with 2 criteria

I have 3 columns:

  • Action Plan
  • Move Row (checkbox)
  • Helper

I need to check the box if the following is met:

  • 2 exact values in the helper column.
  • If the Action Plan is empty.
  • Check the checkbox in the row with the empty Action Plan.

I keep getting an error using this formula if the Action Plan is empty (2nd line):

=IF(COUNTIFS([Action Plan]@row, ISBLANK(@cell)), IF(COUNTIFS(Helper:Helper, Helper@row) > 1, 1))

If not, it is fine (1st line).

Thanks in advance.

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Justin Mauzy

    How is your "Month" column being populated?

    Formula errors are like dominoes - if there is one cell with an error in a range and then you reference it in a different formula, the original error will be displayed in your second formula. I'm curious to see if the error you have right now is actually an error from a different formula. That's where the IFERROR will help... if there are any errors in your JOIN formula, it will prevent this from affecting your IF(AND formula. Does that make sense?

    Can you try updating the JOIN formula just in case?

    =IFERROR(JOIN(Month@row:Team@row), "")

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭

    Hi @Justin Mauzy

    Can you try below formula?

    =IF(AND(ISBLANK([Action Plan]@row), COUNTIFS(Helper:Helper, Helper@row) > 1), 1, 0) 

    Best Regards

    Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/

    Best Regards
    Amit Wadhwani
    , Smartsheet Community Champion
    Smartsheet CoE, Ignatiuz, Inc., Exton, PA

    Did this answer help you? Show some love by marking this answer as "Insightful
    💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/

  • Justin Mauzy
    Justin Mauzy ✭✭✭✭✭

    I am still getting an the following:



  • Hi @Justin Mauzy

    I see that you have a formula in your Helper column. Is it possible that this helper column has an error anywhere in it? If so, since you're referencing the entire column in your current formula, it will present an error.

    Try wrapping an IFERROR statement around your Helper formula:

    =IFERROR(helper formula, "")

    Then see if that updates your Move Row formula!

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Justin Mauzy
    Justin Mauzy ✭✭✭✭✭

    The helper column formula doesn't have any errors. Here is that formula:

    =JOIN(Month@row:Team@row)

    By putting in the IFERROR, it is just going to ignore the errors and doesn't fix the issue.

    Thank you for responding.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Justin Mauzy

    How is your "Month" column being populated?

    Formula errors are like dominoes - if there is one cell with an error in a range and then you reference it in a different formula, the original error will be displayed in your second formula. I'm curious to see if the error you have right now is actually an error from a different formula. That's where the IFERROR will help... if there are any errors in your JOIN formula, it will prevent this from affecting your IF(AND formula. Does that make sense?

    Can you try updating the JOIN formula just in case?

    =IFERROR(JOIN(Month@row:Team@row), "")

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Justin Mauzy
    Justin Mauzy ✭✭✭✭✭

    That worked. I had to do a few things to get it to work.

    Changed the formula in the Move Row column to:

    =IF(COUNTIFS([Action Plan]@row, ISBLANK(@cell)), IF(COUNTIFS(Helper:Helper, Helper@row) > 1, 1))

    I also had to change the ISTEXT to ISBLANK.

    Then I added the IFERROR to the formula in the Helper column to:

    =IFERROR(JOIN(Month@row:Team@row), "")

    Everything works now.


    Thank you for the help.

  • Wonderful, I'm glad you were able to get it all working!

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!