SOLVED

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

Occasional Contributor

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

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 Replies
best response confirmed by rbellotti (Occasional Contributor)
Solution

# Re: How to reference cells when one is populated and one is blank

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

# Re: How to reference cells when one is populated and one is blank

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