Tasks

[Saud]

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

[#2]

@Saud 

What is the difference between "In progress" and "Not completed"?

[#3]

@Hans Vogelaar 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

[#4]

@Saud 

Ah, that's different from

    if any of the cells is not filled, ther phrase appears "not completed"

Let's say you want to look at D2:G2.

=IFS(ISNUMBER(MATCH("off", D2:G2, 0)), "Off", COUNTIF(D2:G2, "<>")=0, "Not Completed", COUNTIF(D2:G2, "=")>0, "In Progress", TRUE, "Completed")

This can be filled down.

[#5]

@Saud 

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"))
    )
)

[#6]

Thank you very much. I appreciate your effort.
but an additional idea occurred to me, which I know would complicate the equation a lot, but would be more subtle. I want to link the formula with today's date or a certain number of days, because if the task is "in progress" or "not completed", but the next day comes and the task is still in progress or not completed, another phrase appears, which is "delayed in task monitoring"
forgive me for this request, which I think will complicate the equation, but I think it will make the tasks monitored with a lot of accuracy, also because i want to monitor tasks on a daily basis
thank you very much again.

[#7]

Also, if the tasks are linked to today's date, I think they can also be linked to the date that has not yet arrived, as the phrase "The task has not yet arrived" appears.

[#8]

Thank you, I appreciate your effort

[#9]

@Saud 

Could you please illustrate on the sample how it is structured, where are dates and where is other information, which part is to check if to filled or not, etc.

[#10]

@Sergei BaklanKindly find attachment photo, i hope the explain its clear

[#11]

@Sergei Baklan I apologize, here you find the correct note

[#12]

@Saud 

Try this formula:

=IFS(ISNUMBER(MATCH("off", B2:E2, 0)), "Off", COUNTIF(B2:E2, "<>")=0, IF(A2<TODAY(), "Delay in Task Monitoring", "Not Completed"), COUNTIF(B2:E2, "=")>0, IF(A2<TODAY(), "Delay in Task Monitoring", "In Progress"), TRUE, "Completed")

[#13]

@Hans Vogelaar  

Thank you, the formula works great. There are simple notes. I attached illustrative a photo.
again, I thank you for your time

[#14]

@Saud 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#15]

@Hans Vogelaar 

yes of course attached workbook

[#16 · Accepted solution]

@Saud 

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")

[#17]

@Hans Vogelaar  

yes, There is a simple thing, but it is not important, and which is if today's date and there are an in one of the cells, sum formula, it will be "in progress" not "not completed".

I really thank you for what you did for me, I appreciate your efforts

[#18]

@Saud 

I thought the formula already did that...

[#19]

@Hans Vogelaar  Try this in Excel your attached before, In the case of the task that is "not completed", add a sum formula in the Total column, the sum will appear as 0 because you have not entered the required numbers yet, the formula will understand that there is a fill in one of the cell, so because it is today's date, the status task will be "in progress".If we can solve it, it will be better, if we can't, that's okay, because the next day, if he doesn't complete the task, it will appear "delay in task monitoring"

[#20]

@Saud 

In that case, you should not include cells with a sum (or other) formula in the range used to determine the status. You can do this by moving column E to the right, to column G. The formulas now refer to B2:F2 etc., excluding the formula.