Looking for a way for the IFS function to return a specific value based on several factors.

[MoonlitAce]

Hello! I know a fair bit about excel formulas but this one is stumping me. I'm looking for a formula that will return a specific value based on what data is found on my table. I think the formula that can do it for me is the IFS function but i just keep running into errors.

Here's what I'm looking to do:

• I have one table of tasks that can either be marked Pass, Fail, or Pending.
• If all tasks are marked Pass, I would like the function to return the value "Pass"
• If any number of tasks are marked Pass with the remaining marked Pending, I would like the function to return the value "Pass - Incomplete"
• If any tasks are marked Fail with the remaining marked Pass, I would like the function to return the value "Fail"
• If any number of tasks are marked Fail with the remaining marked Pending, I would like the function to return the value "Fail - Incomplete"

I'll include a screenshot below of the different possible cases of tables and what the formula should return to give a better visual of what I'm attempting to do. Am I correct in trying to work with the IFS formula? Or is there another formula i should be using to do this?

The formula I'm currently using is

=IFS(COUNTIF(C6:D9,"Pass")*AND(C6:D9="Pass"),"Pass",COUNTIF(C6:D9,"Fail"),"Fail",COUNTIF(C6:D9,"Pending")*AND(C6:D9="Pending"),"Pending")

and it works when the tasks are all Pass, all Pending, or have 1 Fail. But any time i mix Pass and Pending i'm thrown an N/A error and can't seem to find a way to have it look for two separate values.

[#2]

@MoonlitAce 

=LET(rng,C6:D9,
IFS(
AND(COUNTIF(rng,"Pass"),COUNTIF(rng,"<>Pass")=0),
"Pass",
AND(COUNTIF(rng,"Pass")>0,COUNTIF(rng,"Pending")>0,COUNTIFS(rng,"<>Pass",rng,"<>Pending")=0),
"Pass - Incomplete",
AND(COUNTIF(rng,"Fail")>0,COUNTIF(rng,"Pass")>0,COUNTIFS(rng,"<>Fail",rng,"<>Pass")=0),
"Fail",
AND(COUNTIF(rng,"Fail")>0,COUNTIF(rng,"Pending")>0,COUNTIFS(rng,"<>Fail",rng,"<>Pending")=0),
"Fail - Incomplete",
AND(COUNTIF(rng,"Pending"),COUNTIF(rng,"<>Pending")=0),
"Pending",
TRUE,"no such")
)

This formula should return the intended result according to your screenshot with the exception that if any values are "Fail" and the remaining are "Pass" then the formula returns "Fail". According to the screenshot "Fail" should be returned only if all values are "Fail". If you don't have access to the LET function then IFS can be used however it's inconvenient to change the ranges.

=IFS(
AND(COUNTIF(C6:D9,"Pass"),COUNTIF(C6:D9,"<>Pass")=0),
"Pass",
AND(COUNTIF(C6:D9,"Pass")>0,COUNTIF(C6:D9,"Pending")>0,COUNTIFS(C6:D9,"<>Pass",C6:D9,"<>Pending")=0),
"Pass - Incomplete",
AND(COUNTIF(C6:D9,"Fail")>0,COUNTIF(C6:D9,"Pass")>0,COUNTIFS(C6:D9,"<>Fail",C6:D9,"<>Pass")=0),
"Fail",
AND(COUNTIF(C6:D9,"Fail")>0,COUNTIF(C6:D9,"Pending")>0,COUNTIFS(C6:D9,"<>Fail",C6:D9,"<>Pending")=0),
"Fail - Incomplete",
AND(COUNTIF(C6:D9,"Pending"),COUNTIF(C6:D9,"<>Pending")=0),
"Pending",
TRUE,"no such")

[#3]

@MoonlitAce 

If you have access to the LET function, you can cut some corners for the formula.

=LET(
    result, TEXTJOIN(", ", , SORT(UNIQUE(TOCOL(C6:D9)))),
    IF(
        result = "Pass, Pending",
        "Pass - Incomplete",
        IF(
            result = "Fail, Pending",
            "Fail - Incomplete",
            IF(XOR(result = {"Pass", "Fail", "Pending"}), result, "none")
        )
    )
)

[#4]

@OliverScheurich 

aha ! this was a fantastic way to do it and the formula is laid out in a way that makes sense to me ! I didn't even know about the LET function so i'll definitely add that to my list to look more into. thank you very much for your help !

[#5]

the LET function is new to me so i'm definitely going to have to spend more time learning about it. the way your formula is set up is very clean and i don't quite understand how it works but it does just exactly what i'm looking for it to do. thank you for your help ! seeing a fully complete formula like this will help me better understand how to write it myself in the future