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

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"
image.png


...

Tags:
· Share on FacebookShare on Twitter

Best Answer

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


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


    · Share on FacebookShare on Twitter

Answers

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


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


    · Share on FacebookShare on Twitter
  • heyjay
    heyjay ✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/360682#Comment_360682

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

    ...

    · Share on FacebookShare on Twitter
  • Leibel S
    Leibel S ✭✭✭✭✭✭

    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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!