Feb 06 2019 11:11 PM - edited Feb 06 2019 11:19 PM
I have the following table:
O1 | P1 | Q1 | AA | AB | AC | AD | AE |
I | I | I | |||||
1. How can I make it so that if there is a I in either AA or AB then there is a 1 in O1?
I am not sure how to do this.
2. How can I make it so that if there is a I in AE then there is a 1 in P1?
=IF(SUM(COUNTIF($AE,{"i"})),"1","") - this doesn't seem to work properly when I drag down the formula for the entire AE column.
3. How can I make it so that if there are no Is in any column between AC and AD then there is a 1 in Q1?
=IF(SUM(COUNTIF($AC:$AD,{"i"})),"1","") - this doesn't seem to work properly when I drag down the formula for the entire AI column.
Many thanks.
Feb 07 2019 01:42 AM
Solution1: =IF(COUNTIF(AA1:AB1,"I")=1,1,"")
this only returns 1 if there is exactly 1 "I" in AA and AB. If you need it to also return 1 if there is an "I" in both AA and AB:
=IF(COUNTIF(AA1:AB1,"I")>1,1,"")
2: =IF(AE1="I",1,"")
3: =IF(COUNTIF(AC1:AD1,"I")=0,1,"")
Feb 07 2019 08:06 PM
Feb 07 2019 10:11 PM
=IF(COUNTIF(AB5:AI5,"I")+COUNTIF(AB5:AI5,"I")>=1,"",1)
Feb 07 2019 11:03 PM
Feb 07 2019 01:42 AM
Solution1: =IF(COUNTIF(AA1:AB1,"I")=1,1,"")
this only returns 1 if there is exactly 1 "I" in AA and AB. If you need it to also return 1 if there is an "I" in both AA and AB:
=IF(COUNTIF(AA1:AB1,"I")>1,1,"")
2: =IF(AE1="I",1,"")
3: =IF(COUNTIF(AC1:AD1,"I")=0,1,"")