IF formula to check the box column

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

Answers

  • RaffyM
    RaffyM ✭✭✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    RaffyM ✭✭✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    RaffyM ✭✭✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    RaffyM ✭✭✭✭✭
    Options

    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. 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!