How to reference cells when one is populated and one is blank

[rbellotti]

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!

[#2 · Accepted solution]

@rbellotti 

It 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,"")

[#3]

@rbellotti 

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,"")
))