Check a checkbox with 2 criteria

Justin Mauzy

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.

Genevieve P.

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

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/

Justin Mauzy

I am still getting an the following:

Genevieve P.

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

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.

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

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.

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