Forum Discussion
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
MichelleB350 Like this then?
=IFS(COUNTIF(AB27:AH27,"yes"),"yes",COUNTIF(AB27:AH27,"no"),"no",COUNTIF(AB27:AH27,"absent"),"absent")
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"MichelleB350 Like this then?
=IFS(COUNTIF(AB27:AH27,"yes"),"yes",COUNTIF(AB27:AH27,"no"),"no",COUNTIF(AB27:AH27,"absent"),"absent")
