Dec 27 2020 04:24 PM
Need one formula for each of the following:
If $L2 is populated with any text value and $M2 is blank, place a 1 in $AJ2
If $L2 is populated with any text value and $M2 is populated with any text value, place a 1 in $AK2
If $L2 is blank and $M2 is populated with any text value, place a 1 in $AL2
Thank you!
Dec 27 2020 05:24 PM
SolutionIt will be a combination of IF, AND and COUNTA formulas:
If $L2 is populated with any text value and $M2 is blank, place a 1 in $AJ2
=IF(AND(COUNTA(L2)<>0,COUNTA(M2)=0),1,"")
If $L2 is populated with any text value and $M2 is populated with any text value, place a 1 in $AK2
=IF(AND(COUNTA(L2)<>0,COUNTA(M2)<>0),1,"")
If $L2 is blank and $M2 is populated with any text value, place a 1 in $AL2
=IF(AND(COUNTA(L2)=0,COUNTA(M2)<>0),1,"")
Dec 28 2020 01:39 AM
As variant in AJ2
=LET(
k, SEQUENCE(,3),
CHOOSE(k,
IF(ISTEXT($L$2)*ISBLANK($M$2),1,""),
IF(ISTEXT($L$2)*ISTEXT($M$2),1,""),
IF(ISBLANK($L$2)*ISTEXT($M$2),1,"")
))
Dec 27 2020 05:24 PM
SolutionIt will be a combination of IF, AND and COUNTA formulas:
If $L2 is populated with any text value and $M2 is blank, place a 1 in $AJ2
=IF(AND(COUNTA(L2)<>0,COUNTA(M2)=0),1,"")
If $L2 is populated with any text value and $M2 is populated with any text value, place a 1 in $AK2
=IF(AND(COUNTA(L2)<>0,COUNTA(M2)<>0),1,"")
If $L2 is blank and $M2 is populated with any text value, place a 1 in $AL2
=IF(AND(COUNTA(L2)=0,COUNTA(M2)<>0),1,"")