Nested Formulla

Vinton Douglas

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

Paul Newcome

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

Sameer Karkhanis

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")
   )
 )

Vinton Douglas

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

Paul Newcome

Are all 4 of the columns next to each other?

Vinton Douglas

Thanks Paul,

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

Paul Newcome

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?

Vinton Douglas

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

Paul Newcome

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

Vinton Douglas

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.

Paul Newcome

Happy to help. 👍️