if blank and not blank multiple cells

[Manoj K]

Hello,

I have an excel file, which contains many columns. I want answer in one column based on out put in another three columns. i.e. (1) if column "A, B, & C" is blank answer should be blank in Column "D", (2) if column "A" is not blank answer in Column "D" should be as "Under Analysis" , (3) if column "A & B" is not blank answer in Column "D" should be as "Ready" , (4) if column "A, B & C" is not blank answer in Column "D" should be as "Dispatched".

Can any one help me with a formula.

Regards

Manoj

 

[Rick Rothstein]

Here is one way to do it...

=IFERROR(CHOOSE(COUNT(A2:C2),"Under Analysis","Ready","Dispatched"),"")

 

[Manoj K]

Thank you Mr. Rick. It worked well

 

[jimrward]

this only works if the data follows set patterns, if A,B are empty and C contains a value then "Under Analysis" will still be flagged, as the count will be 1, similarily if B=empty and A, C have values then Ready will be flagged

 

[Rick Rothstein]

this only works if the data follows set patterns,

That is what the data in the picture and the meaning behind the headers indicated.

 

[Manoj K]

this only works if the data follows set patterns, if A,B are empty and C contains a value then "Under Analysis" will still be flagged, as the count will be 1, similarily if B=empty and A, C have values then Ready will be flagged

Yes, that is correct. Is there any other formula which can eliminate this error?

 

[Rick Rothstein]

Yes, that is correct. Is there any other formula which can eliminate this error?

You mean your values are not filled in with dates sequentially? If so, why did you mark my message as the "Answer"?

 

[jbartholomew8664]

=IF(AND(C5=0,B5=0,A5=0),"",IF(AND(C5=0,B5=0,A5<>0),"Under Analysis",IF(AND(C5=0,B5<>0),"Ready",IF(C5<>0,"Dispatched")))) Is this what you are looking for? Or does it have the same issue as the choose formula?

1-Apr​			Under Analysis
1-Apr​	2-Apr​		Ready
1-Apr​	2-Apr​	3-Apr​	Dispatched

 

[Manoj K]

this is what i was looking for. Thanks

 

[Rick Rothstein]

this is what i was looking for. Thanks

In that case, you need to change the Answer from my message to the one jbartholomew866 posted.