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

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

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
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 ⬆️"

I am still getting an the following:

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!

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.

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

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!
Help Article Resources
Categories
Check out the Formula Handbook template!