IF formula to check the box column

System

This discussion was created from comments split from: Replacing false value with certain symbol or " ".

RaffyM

Hello @Genevieve P.

Sorry to butt in here. I have a IF formula to check the box column if condition is me but it's not working correctly as to my intension. I read a lot of similar posts in the community that's why I came up with this formula.

Would you mind checking and let me know what is missing. Thanks

In my worksheet, I have a "Affected Owner" column as well as the columns for each process owners' approval. If the affected process owners accepted the request, the checkbox column will be checked. The affected owner column could only be one person or two or more. If only one owner is affected, my formula works perfectly. But my difficulty was when there are at least two owners affected. Using the formula, I want the checkbox only checks if all of them accepted already, not if only one but other is either pending or returned the request.

Here is my formula:

=IF(AND(OR(CONTAINS("John", [Affected Owner]@row), CONTAINS("Alexa", [Affected Owner]@row), CONTAINS("Charles", [Affected Owner]@row), CONTAINS("Kim", [Affected Owner]@row), CONTAINS("Ian", [Affected Owner]@row), CONTAINS("Eli", [Affected Owner]@row)), OR(CONTAINS("Accepted", John@row), CONTAINS("Accepted", Alexa@row), CONTAINS("Accepted", Charles@row), CONTAINS("Accepted", Kim@row), CONTAINS("Accepted", Ian@row), CONTAINS("Accepted", Eli@row))), 1)

Hope you could enlighten me here. Thank you in advance! 😊

Raffy

Genevieve P.

Hey @RaffyM

Happy to help!

We could check each person against their column, but I would suggest it would be easier to Count how many selections were made, and then count how many cells say "Accepted" to see if they match or not.

=IF(COUNTM([Affected Owner]@row) = COUNTIFS(John@row:Eli@row, "Accepted"), 1, 0)

Let me know if that makes sense and works for you!

Cheers,

Genevieve

RaffyM

https://community.smartsheet.com/discussion/comment/405734#Comment_405734

Hello @Genevieve P.

You are awesome, it works perfectly! I should know I can write formula as simple as that because I've been doing trial and error of my IF formula in the last couple of days. 😉

I forgot to mention that there are columns in between of each Affected Owner (Comment Column) for their comments if applicable. I tried adding notes/comments and it doesn't affect the result as long as the comment is not the word "Accepted" as well.

Thank you very much!! 😊

Genevieve P.

So glad to hear it worked for you!

Yes, as long as the cells don't have "Accepted" as the only word, you can have other columns between your ranges as well.

RaffyM

Hello @Genevieve P.

Regarding this if formula

=IF(COUNTM([Affected Owner]@row) = COUNTIFS(John@row:Eli@row, "Accepted"), 1, 0)

is there a way to disregard the columns in between John and Eli? My reason is because they may write the same criteria in the Comment columns, which will give incorrect result.

I tried like this but it gives me invalid data type error

=IF(COUNTM([Affected Owner]@row) = COUNTIFS(AND(John@row, Alexa@row, Charles@row, Kim@row, Ian@row, Eli@row), "Accepted"), 1, 0)

I also tried this but it gives incorrect argument set.

=IF(COUNTM([Affected Owner]@row) = COUNTIFS(John@row, Alexa@row, Charles@row, Kim@row, Ian@row, Eli@row, "Accepted"), 1, 0)

Thank you in advance 😊

Genevieve P.

Hi @RaffyM

The cell will only be counted if it contains the exact phrase "Accepted", meaning it won't include cells that have extra text (e.g. "This says accepted").

However if you need to have other columns in between and they could have a cell that only says "Accepted", you can count each individual reference separately:

IF(John@row = "Accepted", 1, 0)

+ IF(Alexa@row = "Accepted", 1, 0)

+ IF(Charles@row = "Accepted", 1, 0)

+ IF(Kim@row = "Accepted", 1, 0)

+ IF(Ian@row = "Accepted", 1, 0)

+ IF(Eli@row = "Accepted", 1, 0)

=IF(COUNTM([Affected Owner]@row) = (IF(John@row = "Accepted", 1, 0) + IF(Alexa@row = "Accepted", 1, 0) + IF(Charles@row = "Accepted", 1, 0) + IF(Kim@row = "Accepted", 1, 0) + IF(Ian@row = "Accepted", 1, 0) + IF(Eli@row = "Accepted", 1, 0)), 1, 0)

Cheers,

Genevieve

RaffyM

Thanks @Genevieve P. for the input. Though it is not ideal because I have more than 10 range columns to consider. I just thought there is a simple way to write the formula.

Thank you once again. 😊