Formula to return yes, no or do nothing

[justmeok]

Hi all,

I need some help on a formula to return yes, no or leave blank in the following circumstances.
I have a column of 4 rows and these rows will either be blank or have yes or no. I want a formula to do nothing if all cells are blank, or return yes if all cells are yes, or return no if at least one cell is no. The formula is to do nothing if any of the 4 cells are blank as well. In other words 4 x yes are needed for yes, 4 x populated cells with 1 x No returns No otherwise do nothing
I have played around with IF, AND, OR and ISBLANK formulas but I'm getting nowhere
Any help will be greatly appreciated

 

[JeanKim]

what if 1 blank and 1 no?

 

[steve the fish]

Try:

=IF(COUNTIF($A$1:$A$4,""),"",IF(COUNTIF($A$1:$A$4,"no"),"no","yes"))

 

[justmeok]

JeanKim good question All 4 rows need to have an entry which will either be yes or no otherwise the formula must do nothing. Once all 4 rows have an entry then yes will be returned if all 4 rows are yes otherwise no in any row returns no. I hope I have managed to explain a little better?

 

[godsaaint]

Assuming your range is A1-A4, try:

=IF(COUNTIF(A1:A4,""),"",IF(COUNTIF(A1:A4,"NO"),"NO",IF(COUNTIF(A1:A4,"YES")=4,"YES")))

 

[justmeok]

Thank you steve the fish and godsaaint both of your formulas work beautifully I was completely off the mark with my attempts I'm so glad I came here! Kudos to you both

 

[justmeok]

Hi All

I need a modification to either of the above formulas and I'm unsure how to do it. I have to add WIP as one of the entries in the cells. So what I need is

If any of the 4 cells are blank to formula does nothing
If any of the 4 cells have a No then the formula returns "NO"
If any of the cells have WIP then the formula returns "WIP"
If all 4 cells contain Yes then the formula returns "YES"

I hope I have managed to explain clearly!

 

[justmeok]

Re: Formula to return yes, no or do nothing - Can anyone help please????

Hi All

I need a modification to either of the above formulas and I'm unsure how to do it. I have to add WIP as one of the entries in the cells. So what I need is

If any of the 4 cells are blank to formula does nothing
If any of the 4 cells have a No then the formula returns "NO"
If any of the cells have WIP then the formula returns "WIP"
If all 4 cells contain Yes then the formula returns "YES"

I hope I have managed to explain clearly!

I'm stuck and can't figure it out

 

[steve the fish]

Re: Formula to return yes, no or do nothing - Can anyone help please????

There are a couple of problems with what you say. Its possible that both condition 2 (no) and condition 3 (wip) are both true so in this following formula it tests for no first and returns that result if true. Secondly you say contain yes. Does that mean contain or does it mean equal yes? Following formula is equal yes:

=IF(COUNTIF(A1:A4,""),"",IF(COUNTIF(A1:A4,"NO"),"NO",IF(COUNTIF(A1:A4,"WIP"),"WIP",IF(COUNTIF(A1:A4,"YES")=4,"YES","Nothing true"))))

 

[justmeok]

Re: Formula to return yes, no or do nothing - Can anyone help please????

There are a couple of problems with what you say. Its possible that both condition 2 (no) and condition 3 (wip) are both true so in this following formula it tests for no first and returns that result if true. Secondly you say contain yes. Does that mean contain or does it mean equal yes? Following formula is equal yes:

=IF(COUNTIF(A1:A4,""),"",IF(COUNTIF(A1:A4,"NO"),"NO",IF(COUNTIF(A1:A4,"WIP"),"WIP",IF(COUNTIF(A1:A4,"YES")=4,"YES","Nothing true"))))

Thank you so much Steve the fish this is exactly what I needed it to do. Sorry I didn't manage to explain totally clearly but I'm glad you figured out what I meant! You are a man of many talents indeed I really appreciate your help for the second time - you have made my life so much easier!