SOLVED

IFS + Countif formula on a row range

Copper Contributor

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 yes

If no appears every day for 7 days, the final result is no

If absent appears every day for 7 days, the final result is absent 

If it is a combination of absent and no, the final result is no. 

 

If I only have two options (e.g. yes and no) the formula is: 

=IF(COUNTIF(AB27:AH27,"Yes"),"Yes","No")

 

However, as I have three options, I am coming unstuck! I have tried multiple formulas to no avail. 

I would prefer to avoid a VLOOKUP.

 

Here is a visual: 

 

01/06/202302/06/202303/06/202304/06/202305/06/202306/06/202307/06/2023Final Result
YesYesYesYesYesYesNoYes
YesNoNoYesNoYesNoYes
NoNoNoNoNoNoYesYes
NoNoAbsentAbsentAbsentNoNoNo
AbsentAbsentAbsentAbsentNoYesNo

Yes

AbsentAbsentAbsentAbsentAbsentAbsentAbsentAbsent
NoNoNoNoNoNoNoNo

 

Thankyou

 

 

5 Replies

@MichelleB350 Try this:

 

=IFERROR(IFS(COUNTIF(AB27:AH27,"yes"),"yes",COUNTIF(AB27:AH27,"absent"),"absent"),"no")

 

 

 

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"

best response confirmed by MichelleB350 (Copper Contributor)
Solution

@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 :) 

@MichelleB350 

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")
))
1 best response

Accepted Solutions
best response confirmed by MichelleB350 (Copper Contributor)
Solution

@MichelleB350 Like this then?

=IFS(COUNTIF(AB27:AH27,"yes"),"yes",COUNTIF(AB27:AH27,"no"),"no",COUNTIF(AB27:AH27,"absent"),"absent")

View solution in original post