Has or Contains formula help with IF statement
I feel this is simple but I cannot seem to get it to work with IF, HAS, CONTAINS functions. I want a formula that will read through a range of cells on one row at a time, and if a certain condition is met, give me a specific text back. See photo - any suggestions?
fyi: the 87.8% is dynamic, so the number changes every day automatically.
Best Answer
-
Hi,
I hope you're well and safe!
To add to Nathan's excellent advice/answer.
Try something like this. (Update column names to match yours)=IF(COUNTIFS([Column 1]@row:[Column 35]@row, >1) > 0, "Hold", IF(COUNTIFS([Column 1]@row:[Column 35]@row, >0.878) > 0, "Deliberate", IF(COUNTIFS([Column 1]@row:[Column 35]@row, <0.878) > 0, "Approve")))
Did that work/help?
I hope that helps!
Be safe, and have a fantastic day!
Best,
Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD
✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi @Matthew Campbell, please let me know if this formula will work for you:
=IF( OR([C1]@row > 100, [C2]@row > 100, [C3]@row > 100, [C4]@row > 100, [C5]@row > 100), "Hold", IF( OR([C1]@row > 87.8, [C2]@row > 87.8, [C3]@row > 87.8, [C4]@row > 87.8, [C5]@row > 87.8), "Deliberate", "Approve" ) )This formula assumes that your columns are named "C1", "C2", "C3", etc. So you'll have to update this formula to use the real column names from your sheet.
Hope this works for you!
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com) (LinkedIn)
SSFeatures makes Smartsheet way easier to use and it saves you hours of work every week. It adds essential features into Smartsheet to save you time. For example: — Auto Sorting — Sorting with Filters — Report PDF Generation — Copy and Paste Conditional Formats — Copy and Paste Automation Workflows — Column Manager — and so many more.
-
Hey @SSFeatures
This works, and is helpful! I had done something similar, but was really hoping I could capture an entire range instead of the If,OR statements for each individual column, only because I have 30+ columns to do this with. Is there anyway we can do an IF statement that captures the range instead of individual OR statements for each column? (ie: C1:C30 for your example)
ie: IF(HAS(C1:C30, >87, Deliberate —→ Something like this but that actually works
-
Hi,
I hope you're well and safe!
To add to Nathan's excellent advice/answer.
Try something like this. (Update column names to match yours)=IF(COUNTIFS([Column 1]@row:[Column 35]@row, >1) > 0, "Hold", IF(COUNTIFS([Column 1]@row:[Column 35]@row, >0.878) > 0, "Deliberate", IF(COUNTIFS([Column 1]@row:[Column 35]@row, <0.878) > 0, "Approve")))
Did that work/help?
I hope that helps!
Be safe, and have a fantastic day!
Best,
Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD
✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Between you and Nathan, y'all saved me hours and many pitfalls. This formula makes perfect sense, I never used Countifs like this so thank you for teaching me!
-
Excellent!
You're more than welcome!
✅ Remember! Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution/help to answer! I appreciate it, thank you! 🙏SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Wow that's awesome @Andrée Starå!
I didn't know you could do that!
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com) (LinkedIn)
SSFeatures makes Smartsheet way easier to use and it saves you hours of work every week. It adds essential features into Smartsheet to save you time. For example: — Auto Sorting — Sorting with Filters — Report PDF Generation — Copy and Paste Conditional Formats — Copy and Paste Automation Workflows — Column Manager — and so many more.
-
Thank you, and thanks for the kind words!
✅ Remember! Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution/help to answer! I appreciate it, thank you! 🙏SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
Check out the Formula Handbook template!