Display minimum of numbers or text if range criteria is met

[nbawa27]

=IF(AND(A4:A6>=0,A4:A6<=5,COUNTIF(A4:A6,"NS")),MIN(A4:A6),"NS")

This equation doesn't seem to capture the full logic of what I'm trying to accomplish. In column A, we have a validated data cell where the only allowed values are: 1,3,5, NS. In the column where the above equation fits, I would like the minimum value of the range to be displayed unless all of the range values are "NS". If the range has any combination of values including "NS" and numbers, only the minimum of the numbers should be displayed. Again, the equation should produce "NS" only if all of the range values are "NS".

Any help would be very much appreciated. Thank you in advanced for your efforts!

 

[Matty]

Welcome to the Board!

If I follow, try:

=IF(COUNTIF(A4:A6,"NS")=3,"NS",MIN(A4:A6))

Matty

 

[nbawa27]

Matty,

Thank you for the welcome. That is a brilliant solution. I appreciate your prompt and helpful reply. Hope I can repay one soon.

 

[Matty]

Matty,

Thank you for the welcome. That is a brilliant solution. I appreciate your prompt and helpful reply. Hope I can repay one soon.

Glad I could help.

Matty