Forum Discussion
Solved
IFS + Countif formula on a row range
Hello, I am trying to collate data for over 100 items over a seven-day period to return a final result of yes, no or absent. If yes appears one time during the 7 day period, the final result is ye...
MichelleB350 Like this then?
=IFS(COUNTIF(AB27:AH27,"yes"),"yes",COUNTIF(AB27:AH27,"no"),"no",COUNTIF(AB27:AH27,"absent"),"absent")
It is very close! The final piece of information, I didn't consider earlier (I have amended my original post to include) is:
If the combination over the 7 days is part absent and part no, the final result is no.
The current formula returns a result in this scenario as "absent"
If I change the return to "no", then when all 7 days are absent, it also returns "no"
MichelleB350 Like this then?
=IFS(COUNTIF(AB27:AH27,"yes"),"yes",COUNTIF(AB27:AH27,"no"),"no",COUNTIF(AB27:AH27,"absent"),"absent")Thankyou Riny_van_Eekelen
This is the solution to my problem 🙂
From performance point of view it's better to use nested IF, like
=IF(COUNTIF(AB27:AH27,"yes"),"yes", IF(COUNTIF(AB27:AH27,"no"),"no", IF(COUNTIF(AB27:AH27,"absent"),"absent", "have no idea") ))
