Help with Multi IF Formula

James Yewen

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

Dale Murphy

@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

James Yewen

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