IF AND/OR for Text Return

Hello,

I have a tracking sheet that has three phases and each phase has a status, either "Not Started", "In Progress", or "Completed". I have an overall status column so I want to create a formula for the overall status that will return the value of "Not Started", if all phases are not started, "In Progress" if any of the phases are in progress, and "Completed" if the final phase is completed.

I'm not sure what the formula would be for this or if I would be better off using symbols.

=IF(AND([1 Rough Draft Writing Status]7, "Not Started", [2 Rough Draft Review Status]7, "Not Started", [4 PDF Review Status]7, "Not Started"), "Not Started", "In Progress")

Thank you,

Kim

Find more posts tagged with

Formulas

Comments

Nic Larsen

Try this:

=IF([4 PDF Review Status]@row = "Completed", "Completed", IF(OR([1 Rough Draft Writing Status]@row = "In Progress", [2 Rough Draft Writing Status]@row = "In Progress", [4 PDF Review Status]@row = "In Progress"), "In Progress", "Not Started"))

kimberleeadam

I'm still getting a #UNPARSEABLE error

=IF([4 PDF Review Status]7 = "Completed", "Completed", IF(OR([1 Rough Draft Writing Status]7 = "In Progress", [2 Rough Draft Writing Status]7 = "In Progress", [4 PDF Review Status]7 = "In Progress"), "In Progress", "Not Started"))

Andrée Starå

Hi Kim,

Can you maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

kimberleeadam

Yes, I wouldn't mind sharing at all. I don't have any sensitive information in it so no problem. Thank you so much!

FYI... I was also trying to play around with colored dots so in row 7, you'll see that... row 8 still has the text. To be honest, I kind of like the dots but whatever works would be very cool.

Thank you again!

Andrée Starå

Happy to help!

Try something like this.

Let's start with this and then we can update it to show RYGG later.

I've added so the formula will show In Progress if everything isn't Completed but at least one is.

=IF([4 PDF Review Status]@row = "Completed"; "Completed"; IF(OR(COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row; "In Progress") > 0; COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row; "Completed") > 0); "In Progress"; IF(COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row; "Not Started") = 3; "Not Started")))

The same version but with the below changes for your and others convenience.

=IF([4 PDF Review Status]@row = "Completed", "Completed", IF(OR(COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row, "In Progress") > 0, COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row, "Completed") > 0), "In Progress", IF(COUNTIF([1 Rough Draft Writing Status]@row:[4 PDF Review Status]@row, "Not Started") = 3, "Not Started")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Best,

Andrée

kimberleeadam

This is great! And works awesome! I was able to replace the text with the RYGG and it worked just as well. Thank you so much for your help!

Andrée Starå

Excellent!

Happy to help!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…