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

heyjay

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

Leibel S

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

Leibel S

@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

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

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