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.

Find more posts tagged with

Accepted answers

Andrée Starå

https://community.smartsheet.com/discussion/139553/has-or-contains-formula-help-with-if-statement

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!

All comments

SSFeatures

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!

Matthew Campbell

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

Andrée Starå

https://community.smartsheet.com/discussion/139553/has-or-contains-formula-help-with-if-statement

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)

Matthew Campbell

@Andrée Starå

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!

Andrée Starå

https://community.smartsheet.com/discussion/comment/484942#Comment_484942

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! 🙏

SSFeatures

Wow that's awesome @Andrée Starå!

I didn't know you could do that!

Andrée Starå

https://community.smartsheet.com/discussion/comment/484983#Comment_484983

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! 🙏

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.