Forum Discussion

MoonlitAce's avatar
MoonlitAce
Copper Contributor
Oct 31, 2023

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

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 tha...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 31, 2023

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