SOLVED

# IFS + Countif formula on a row range

Copper Contributor

# 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 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/2023 02/06/2023 03/06/2023 04/06/2023 05/06/2023 06/06/2023 07/06/2023 Final Result Yes Yes Yes Yes Yes Yes No Yes Yes No No Yes No Yes No Yes No No No No No No Yes Yes No No Absent Absent Absent No No No Absent Absent Absent Absent No Yes No Yes Absent Absent Absent Absent Absent Absent Absent Absent No No No No No No No No

Thankyou

5 Replies

# Re: IFS + Countif formula on a row range

@MichelleB350 Try this:

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

# Re: IFS + Countif formula on a row range

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

# Re: IFS + Countif formula on a row range

@MichelleB350 Like this then?

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

# Re: IFS + Countif formula on a row range

Thankyou @Riny_van_Eekelen

This is the solution to my problem :)

# Re: IFS + Countif formula on a row range

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

# Re: IFS + Countif formula on a row range

@MichelleB350 Like this then?

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