Return specific text based on multiple cells

[jedilefty]

Hello everyone,

I'm having trouble with a formula. I'm trying to display a certain text based on the values of 4 other cells. Example: based on the text in cells A1 - D1 (which is just a yes or no), I want auto text populated in E1.

 

[Joe4]

What is the logic for the formula you want created?
What are the conditions that it should show a "Y"?
Is it only when all four cells are also "Y"?

If so, you can use:

=IF(COUNTIF(A2:D2,"Y")=4,"Y","N")

 

[DanteAmor]

If with an N the result is N, it can be:

=IF(COUNTIF(A2:D2,"N"),"N","Y")

 

[Joe4]

If with an N the result is N, it can be:

=IF(COUNTIF(A2:D2,"N"),"N","Y")

The only caveat there, is if there is potential for it to be be blank (neither an "N" or a "Y" for an entry).
If it requires 4 "Y"s to be a "Y", then that wouldn't work.

But we do not know the exact possibilities and requirements, so your guess is as good as mine!

 

[jedilefty]

What is the logic for the formula you want created?
What are the conditions that it should show a "Y"?
Is it only when all four cells are also "Y"?

If so, you can use:

=IF(COUNTIF(A2:D2,"Y")=4,"Y","N")

Joe4 I appreciate your help on this. I'm actually using this to determine if a shipping package is completed for orders. I used the formula you provided and it works great. I do have another question though. How would i modify this formula to incorporate another column that isn't necessarily required for each package. So I have columns A:D that are always needed to complete the package, however I added column E for additional testing that some orders require. So i would like column F to show the output "create shipper" or "not complete" based on columns A:E with "Y" and column E with either "Y' or "N/A"

If i didn't explain well enough i can provide a picture of what i'm trying to accomplish.

 

[Joe4]

If i didn't explain well enough i can provide a picture of what i'm trying to accomplish.

Yes, please provide a picture of all the different combinations and expected outputs for each one.

 

[jedilefty]

Yes, please provide a picture of all the different combinations and expected outputs for each one.

So the input in columns E:I will determine the output in column A. Column E:H is only "Y" or "N" while column I can have either "Y", "N" or "#N/A. I would like the output in column A to display "Not Complete" if any cell between column E:H has a "N" and column I has a "N" or "#N/A"
It should display "Create Shipper" if column E:H all have "Y" and column I either "Y" or "#N/A"

 

[Joe4]

Is the value in column I the literal text value "#N/A", or is it the error code #N/A (it makes a BIG difference!).

If you aren't sure, find some cell that has that value in it. Let's say it is I50. Enter this formula in any cell and see what it returns:
=ISNA(I50)
If that returns TRUE, then you are dealing with the erorr code and not a literal text value.

If it is a literal text value, this formula should work (example is for row 45):

=IF(AND(COUNTIF(E45:H45,"Y")=4,OR(I45="Y",I45="#N/A")),"Create Shipper","Not complete")

If it is an error code, the formula will end up being a bit more complex.

 

[jedilefty]

Is the value in column I the literal text value "#N/A", or is it the error code #N/A (it makes a BIG difference!).

If you aren't sure, find some cell that has that value in it. Let's say it is I50. Enter this formula in any cell and see what it returns:
=ISNA(I50)
If that returns TRUE, then you are dealing with the erorr code and not a literal text value.

If it is a literal text value, this formula should work (example is for row 45):

=IF(AND(COUNTIF(E45:H45,"Y")=4,OR(I45="Y",I45="#N/A")),"Create Shipper","Not complete")

If it is an error code, the formula will end up being a bit more complex.

Sorry I forgot to mention all inputs in columns E:I are literal text values. So the formula provided works perfectly. I appreciate the help!!

 

[Joe4]

Sorry I forgot to mention all inputs in columns E:I are literal text values. So the formula provided works perfectly. I appreciate the help!!

Excellent!
You are welcome.