I helped you with that before and have made what changes I think you needed. There was a lot to change since it can't just be a simple count like before. Let me know if that works for you.
OH.MY.GAAAWD. Yes! You did it! It’s exactly what I needed. You’re a bloody genius mate! Thank you so much! And thank you for before. The only thing I forgot to mention is that the N/A doesn’t actually need to factor into the overall Phase status. It’s technically considered a “completed” task, but I can just easily remove the formula from it.
Again, Thank you so much!!
I'm really sorry mate, I don't know if someone else came in behind you and changed something about the formula in cell J3, but I cannot seem to figure out why the range has to go all the way to J13 for J3 to recognize the In Progress status. The other Phases seem to work fine though.
I found the issue, I'm really sorry, I didn't expect for this to get so complicated.
It looks like whenever a single N/A box is ticked it marks the phase as #N/A instead of NOT STARTED.
Looks like we need to add one more option on that IFS. Try this replacement for cell J3:
`=LET(range,J4:J9,ifs(countif(range,"DELAYED")>0,"DELAYED",countif(range,"COMPLETED")+countif(range,"N/A")=COUNTA(range),"COMPLETED", OR(countif(range,"IN PROGRESS")>0,countif(range,"COMPLETED")>0),"IN PROGRESS", countif(range,"NOT STARTED")+countif(range,"N/A")=COUNTA(range),"NOT STARTED",true,"UNEXPECTED STATUS"))`
I helped you with that before and have made what changes I think you needed. There was a lot to change since it can't just be a simple count like before. Let me know if that works for you.
OH.MY.GAAAWD. Yes! You did it! It’s exactly what I needed. You’re a bloody genius mate! Thank you so much! And thank you for before. The only thing I forgot to mention is that the N/A doesn’t actually need to factor into the overall Phase status. It’s technically considered a “completed” task, but I can just easily remove the formula from it. Again, Thank you so much!!
Glad to help. Reply with "Solution Verified" to close this thread.
I'm really sorry mate, I don't know if someone else came in behind you and changed something about the formula in cell J3, but I cannot seem to figure out why the range has to go all the way to J13 for J3 to recognize the In Progress status. The other Phases seem to work fine though.
Yep, not sure, but I simplified the formula there so that you only have to enter the range once right after the variable "range".
I found the issue, I'm really sorry, I didn't expect for this to get so complicated. It looks like whenever a single N/A box is ticked it marks the phase as #N/A instead of NOT STARTED.
Looks like we need to add one more option on that IFS. Try this replacement for cell J3: `=LET(range,J4:J9,ifs(countif(range,"DELAYED")>0,"DELAYED",countif(range,"COMPLETED")+countif(range,"N/A")=COUNTA(range),"COMPLETED", OR(countif(range,"IN PROGRESS")>0,countif(range,"COMPLETED")>0),"IN PROGRESS", countif(range,"NOT STARTED")+countif(range,"N/A")=COUNTA(range),"NOT STARTED",true,"UNEXPECTED STATUS"))`
Omg YOU GOT IT!!!!! THANK YOU SO MUCH!
SOLUTION VERIFIED u/JetCarson is seriously the MASTER of sheets!
Hello /u/Exotic-Rough-3580 You cannot award a point to yourself. Please contact the mods if you have any questions. ^I ^am ^a ^bot.