# Formula to return yes, no or do nothing

#### justmeok

##### New Member
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

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
what if 1 blank and 1 no?

Try:

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

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?

Assuming your range is A1-A4, try:

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

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

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!

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

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

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!

Replies
2
Views
50
Replies
3
Views
182
Replies
5
Views
400
Replies
7
Views
188
Replies
1
Views
206

1,203,111
Messages
6,053,569
Members
444,673
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back