IF Statement Help

[QTHUYNH]

Hi, All!

I spent the entire day trying to figure out the best way but got stuck. Hoping you all can point me in the right direction.

I have a table with four columns (Original, Revised, Completion, and Status). I want the formula to be in the Status column.

Basically, what I'm looking to do is return a specific text based on what's in the cell(s) in the Original, Revised, and Completion columns.

If there is a value in the cell of the Original column, but nothing in the other two columns, then return "On Track" in the Status column.

If there are values in the Original and Revised columns, then return "Revised" in the Status column.

If there are values in all three columns, then return "Completed" in the Status column.

If all three columns are blank, then the status column should be blank as well.

Example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Original[/TD]
[TD]Revised[/TD]
[TD]Completion[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]<blank>[/TD]
[TD]<blank>[/TD]
[TD]<blank>[/TD]
[TD]<blank>[/TD]
[/TR]
[TR]
[TD]10/1/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]On Track[/TD]
[/TR]
[TR]
[TD]10/1/2017[/TD]
[TD]10/15/2017[/TD]
[TD][/TD]
[TD]Revised[/TD]
[/TR]
[TR]
[TD]10/1/2017[/TD]
[TD]10/15/2017[/TD]
[TD]10/16/2017[/TD]
[TD]Completed[/TD]
[/TR]
</tbody>[/TABLE]

 

[dreid1011]

Welcome to the board. Perhaps this will do what you want:

=IF(COUNT($A2:$C2)=3,"Completed",IF(COUNT($A2:$C2)=2,"Revised",IF(COUNT($A2:$C2)=1,"On Track","")))

Edit: However, this does not take into account which column the values are in. It is assuming that columns B and C will NOT have any value if column A is blank. So, if a value is input into columns B or C instead of A, it will still return "On Track".

 

[Marcílio_Lobão]

QTHUYNH, Good evening.

Try to use:

D2 --> =IF(A2<>"",IF(B2<>"",IF(C2<>"","Completed","Revised"),"On Track"),"")

Please, tell us if it worked as desired.
I hope it helps.

 

[QTHUYNH]

Welcome to the board. Perhaps this will do what you want:

=IF(COUNT($A2:$C2)=3,"Completed",IF(COUNT($A2:$C2)=2,"Revised",IF(COUNT($A2:$C2)=1,"On Track","")))

Edit: However, this does not take into account which column the values are in. It is assuming that columns B and C will NOT have any value if column A is blank. So, if a value is input into columns B or C instead of A, it will still return "On Track".

That formula works, but just like you mentioned it only works if one of the cells have values in it. Would a OR/AND statement work? What if the cell has a combination of text and numbers? The COUNT function only works if it's an integer.

 

[QTHUYNH]

QTHUYNH, Good evening.

Try to use:

D2 --> =IF(A2<>"",IF(B2<>"",IF(C2<>"","Completed","Revised"),"On Track"),"")

Please, tell us if it worked as desired.
I hope it helps.

This works!!! Thank you very much!

 

[dreid1011]

That formula works, but just like you mentioned it only works if one of the cells have values in it. Would a OR/AND statement work? What if the cell has a combination of text and numbers? The COUNT function only works if it's an integer.

Try the formula Marcilio posted in #3 .