SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2017149%22%20slang%3D%22en-US%22%3EHow%20to%20reference%20cells%20when%20one%20is%20populated%20and%20one%20is%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2017149%22%20slang%3D%22en-US%22%3E%3CP%3ENeed%20one%20formula%20for%20each%20of%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20%24L2%20is%20populated%20with%20any%20text%20value%20and%20%24M2%20is%20blank%2C%20place%20a%201%20in%20%24AJ2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20%24L2%20is%20populated%20with%20any%20text%20value%20and%20%24M2%20is%20populated%20with%20any%20text%20value%2C%20place%20a%201%20in%20%24AK2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20%24L2%20is%20blank%20and%20%24M2%20is%20populated%20with%20any%20text%20value%2C%20place%20a%201%20in%20%24AL2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2017149%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2017198%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20reference%20cells%20when%20one%20is%20populated%20and%20one%20is%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2017198%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460394%22%20target%3D%22_blank%22%3E%40rbellotti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20will%20be%20a%20combination%20of%20IF%2C%20AND%20and%20COUNTA%20formulas%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20%24L2%20is%20populated%20with%20any%20text%20value%20and%20%24M2%20is%20blank%2C%20place%20a%201%20in%20%24AJ2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(AND(COUNTA(L2)%26lt%3B%26gt%3B0%2CCOUNTA(M2)%3D0)%2C1%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20%24L2%20is%20populated%20with%20any%20text%20value%20and%20%24M2%20is%20populated%20with%20any%20text%20value%2C%20place%20a%201%20in%20%24AK2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(AND(COUNTA(L2)%26lt%3B%26gt%3B0%2CCOUNTA(M2)%26lt%3B%26gt%3B0)%2C1%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20%24L2%20is%20blank%20and%20%24M2%20is%20populated%20with%20any%20text%20value%2C%20place%20a%201%20in%20%24AL2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(AND(COUNTA(L2)%3D0%2CCOUNTA(M2)%26lt%3B%26gt%3B0)%2C1%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2017661%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20reference%20cells%20when%20one%20is%20populated%20and%20one%20is%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2017661%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460394%22%20target%3D%22_blank%22%3E%40rbellotti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20in%20AJ2%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0A%20%20k%2C%20SEQUENCE(%2C3)%2C%0A%20%20CHOOSE(k%2C%0A%20%20%20%20IF(ISTEXT(%24L%242)*ISBLANK(%24M%242)%2C1%2C%22%22)%2C%0A%20%20%20%20IF(ISTEXT(%24L%242)*ISTEXT(%24M%242)%2C1%2C%22%22)%2C%0A%20%20%20%20IF(ISBLANK(%24L%242)*ISTEXT(%24M%242)%2C1%2C%22%22)%0A))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

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

 

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