Nested Formulla

Options

Hello,

I am trying to create a formulla, perhaps with IF(AND). I have 4 columns. Each column with the following dropdown: Yes, No, Sometime, N/A. The columns are:

Criteria A

Criteria B

Criteria C

Criteria D


What i need to happen is the following:

If criteria A to B are all "yes", then the overall answer should be "yes"

If all criteria are "no', then the overall answer should be "no"

if some of the criteria contains "yes" while other contains "no", then the answer should be "sometime"

If the criteria contains N/A, this should not affect the overall answer from the previous criteria discussed above.

Any help would be grateful.

Thanks in advance

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Ok. In that case you would have something like this:


    =IF(COUNTIFS([Criteria A]@row:[Criteria D]@row, @cell = "N/A") = 4, "N/A", IF(COUNTIFS([Criteria A]@row:[Criteria D]@row, OR(@cell = "No", @cell = "N/A")) = 4, "No", IF(COUNTIFS([Criteria A]@row:[Criteria D]@row, OR(@cell = "Yes", @cell = "N/A")) = 4, "Yes", "Sometimes")))


    If they are all N/A then N/A

    If they are a mix of No and N/A then No

    If they are a mix of Yes and N/A then Yes

    If all else is false (yes and no mixed) then Sometimes

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!