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"
...
Tags:
Best Answer
-
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
-
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")
-
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")
...
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!