Help with Multi IF Formula
Hello,
Relatively new to SmartSheet and formulas and have come across a block.. needing some help from you wonderful people :)
I have a "Site" dropdown column with "SiteA" "SiteB" and "SiteC" as options. then a status column for each of the 3 sites (as below)
What I need is a formula that looks at what sites are in the 'Site' column, then for it to look at the status columns and return Approved or Declined in the final status columns. Sometimes one site may need to approved/decline, sometimes two etc. Got it working for a single site.. fell apart when trying two sites.
This is the formula I tried for two sites: =IF(Site@row = "SiteA", IF([SiteA Status]@row = "Approved", "Approved", "Declined")), IF(Site@row = "SiteA", "SiteB", IF([SiteA Status]@row = "Approved", IF([SiteB Status]@row = "Approved", "Approved", "Declined"))))
Any help is most appreciated.
James
Answers
-
@James Yewen I don't think you say explicitly, but I am assuming that if any site declines, then final status is declined? Try this:
=IF((COUNTIF([siteA status]@row:[siteC status]@row, ="Declined")) > 0, "Declined", IF((COUNTIF([SiteA status]@row:[SiteC status]@row, ="approved")) > 0, "Approved", ""))
Essentially: count how many declines you have; if there are any set Final Status to declined (and stop evaluating). (If no declines) check for approvals, and if there are any set Final Status to approved. Otherwise, leave blank.
Cheers,
dm
-
Hi Dale,
Yes that is the case, not to want to throw a possible spanner in the works.. but what if there was to be multiple possibilities for statuses? I.E: Approved, Declined, Non-FFF Approved. The same logic where if any one site doesn't approve the final status would be either declined or Non-FFF Approved (if two approve, one declines final is declined. Two approve, one says non-fff then final is Non-FF Approved)
Thanks for your help
James
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!