Can you help me simply this formula. I think I can use "And" and "Or', I just dont know how.

Options
heyjay
heyjay ✭✭✭✭✭
edited 02/06/23 in Formulas and Functions
=IF(sum(


COUNTIFS({ML student status}, "No",{ML status}, "Qualified occupied"),
COUNTIFS({ML student status}, "No",{ML status}, "UU Pre-Approved by Compliance"),
COUNTIFS({ML student status}, "No",{ML status}, "QV Pre-Approved by Compliance"),
COUNTIFS({ML student status}, "No",{ML status}, "Unit is in Rehab"),


COUNTIFS({ML OI}, "Yes",{ML status}, "Qualified occupied"),
COUNTIFS({ML OI}, "Yes",{ML status}, "UU Pre-Approved by Compliance"),
COUNTIFS({ML OI}, "Yes",{ML status}, "QV Pre-Approved by Compliance"),
COUNTIFS({ML OI}, "Yes",{ML status}, "Unit is in Rehab"),


COUNTIFS({ML rentover}, "Yes",{ML student status} ,"Qualified occupied"),
COUNTIFS({ML rentover}, "Yes",{ML status}, "UU Pre-Approved by Compliance"),
COUNTIFS({ML rentover}, "Yes",{ML status}, "QV Pre-Approved by Compliance"),
COUNTIFS({ML rentover}, "Yes",{ML status}, "Unit is in Rehab"),


COUNTIFS({ML Expired Cert}, "Yes",{ML status}, "Qualified occupied"),
COUNTIFS({ML Expired Cert}, "Yes",{ML status}, "UU Pre-Approved by Compliance"),
COUNTIFS({ML Expired Cert}, "Yes",{ML status}, "QV Pre-Approved by Compliance"),
COUNTIFS({ML Expired Cert}, "Yes",{ML status}, "Unit is in Rehab"),


COUNTIFS({ML Signed After}, "Yes",{ML status}, "Qualified occupied"),
COUNTIFS({ML Signed After}, "Yes",{ML status}, "UU Pre-Approved by Compliance"),
COUNTIFS({ML Signed After}, "Yes",{ML status}, "QV Pre-Approved by Compliance"),
COUNTIFS({ML Signed After}, "Yes",{ML status}, "Unit is in Rehab")


) > 0, "Yes", "No")

Status to check are

"UU Pre-Approved by Compliance"
"Qualified Occupied"
"QV Pre-Approved by Compliance"
"Unit is in Rehab"


...

Tags:

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options


    @heyjay

    See below one option. This can be done using OR functions as well, this is just shorter

    =IF(SUM(
    COUNTIFS({ML student status}, "No",{ML student status}, CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab")),
    COUNTIFS({ML OI}, "Yes",{ML student status}, CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab")),
    COUNTIFS({ML rentover}, "Yes",{ML student status}, CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab")),
    COUNTIFS({ML Expired Cert}, "Yes",{ML student status}, CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab")),
    COUNTIFS({ML Signed After}, "Yes",{ML student status}, CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab"))
    ) > 0, "Yes", "No")
    


Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options


    @heyjay

    See below one option. This can be done using OR functions as well, this is just shorter

    =IF(SUM(
    COUNTIFS({ML student status}, "No",{ML student status}, CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab")),
    COUNTIFS({ML OI}, "Yes",{ML student status}, CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab")),
    COUNTIFS({ML rentover}, "Yes",{ML student status}, CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab")),
    COUNTIFS({ML Expired Cert}, "Yes",{ML student status}, CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab")),
    COUNTIFS({ML Signed After}, "Yes",{ML student status}, CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab"))
    ) > 0, "Yes", "No")
    


  • heyjay
    heyjay ✭✭✭✭✭
    Options

    Can you please explain how/why is your contains@cell works? I am surprised.

    CONTAINS(@cell,"Qualified occupied UU Pre-Approved by Compliance QV Pre-Approved by Compliance Unit is in Rehab")
    

    ...

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    It is checking if the 'ML student status' is found within that string.

    that string is just a combination of all your statuses.

    This does not always work, if you have another status that would be found within this string (e.g just the word 'qualified', then it would also end up getting counted

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!