# Nested Formulla

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.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭
edited 08/18/22

I assume you meant to say if criteria A to D are all "yes" then overall answer should be "yes"

```=IF( AND([Criteria A]@row = "Yes",[Criteria B]@row = "Yes", [Criteria C]@row = "Yes",[Criteria D]@row = "Yes"), "Yes",
IF(AND([Criteria A]@row = "No",[Criteria B]@row = "No", [Criteria C]@row = "No",[Criteria D]@row = "No"), "No",
IF(AND([Criteria A]@row <> "N/A",[Criteria B]@row <> "N/A", [Criteria C]@row <> "N/A",[Criteria D]@row <> "N/A"), "Sometime", "N/A")
)
)
```
• Hi Sameer,

Thanks a lot. I am only finding one glitch and perhaps I did a poor job at explaining the outcome needed. The N/A when incorporated in the formula you created gives an overall status of N/A. I do not need the N/A to impact on the answer except when all the dropdown answers are N/A. The weight should be on the "Yes" or "No. So if dropdown contain yes and N/A, the status should be "Yes". If the dropdown answers contain "no" and N/A the status should be "no". If the dropdown contains "No", "yes" and N/A, the status should be "sometimes". If the dropdown are all N/A, then the status should be N/A.

Thanks again and grateful if you could amend

• ✭✭✭✭✭✭

Are all 4 of the columns next to each other?

• Thanks Paul,

I have the deliveries in rows, but I can reconstruct to place in columns beside each other.

• ✭✭✭✭✭✭

In your original post you mentioned having 4 dropdown columns and wanting to compare those 4 columns to each other. Has your structure changed since your original post?

• Thanks Paul. This has not changed. The 4 columns remain the same. They are beside each other.

• ✭✭✭✭✭✭

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

• Hi Paul. Just want to say that I tested the formulla throughout the weekend and it works like a charm. Thanks very much for your assistance.

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!