Forum Discussion
Tasks
I want a function for multiple conditions where it says that if a certain number of cells are filled, knowing that there are numbers and text in those cells, if all these cells are filled the phrase appears "Completed", and if a number of cells are filled the phrase appears "in progress", and if any of the cells is not filled, ther phrase appears "not completed" also appears here. Another condition is that if there is a certain text, if it is entered by the person concerned, such as "off", the phrase "off" appears in the status of tasks
Does this work?
=IFS(ISNUMBER(MATCH("off", B2:G2, 0)), "Off", COUNTIF(B2:G2, "<>")=0, IF(A2<TODAY(), "Delay in Task Monitoring", "Not Completed"), COUNTIF(B2:G2, "=")>0, IF(A2<TODAY(), "Delay in Task Monitoring", IF(A2=TODAY(), "In Progress", "Not Yet Due")), TRUE, "Completed")
30 Replies
What is the difference between "In progress" and "Not completed"?
HansVogelaar The difference between 'Not completed' and 'in progress' is that in the first phrase no filled in cell in the range of cells is filled, while in seconde phrase in progress a part of the range of cells is filled
As variant
=IF( SUM(--(range = "off")), "Off", LET( n, COLUMNS(range), k, SUM(--ISBLANK(range)), IF(k = n, "Not completed", IF(k = 0, "Completed", "In progress")) ) )
