Check a checkbox with 2 criteria

Options

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 Admin
    Answer ✓
    Options

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

Answers

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭
    Options

    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 Software, Exton, PA

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


    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

  • Justin Mauzy
    Justin Mauzy ✭✭✭✭✭
    Options

    I am still getting an the following:



  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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!

  • Justin Mauzy
    Justin Mauzy ✭✭✭✭✭
    Options

    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 Admin
    Answer ✓
    Options

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

  • Justin Mauzy
    Justin Mauzy ✭✭✭✭✭
    Options

    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!