If Multiple Condition and Nested CountIf Formula Not Working

[TropicalMagic]

Hi all,



I would like to:



Check if row value in Column AM = "APAC"

Check if row value in Column AR = "APAC"

Check if row value in Column AJ has partial string match containing "OFIA"

Check if row value in Column AJ has partial string match containing "Intra-Asia"



If all above conditions are true, return 1 in Column BM, else return 0 in Column BM.



However, my formula entered below is not returning any 1s, only 0s:


Range("BM2")

=IF(AND(AM2="APAC",AR2="APAC",COUNTIF(AJ2,"*OFIA*"),COUNTIF(AJ2,"*Intra-Asia*")),1,0)



Many thanks!

 

[Fluff]

Should the 2 conditions for AJ2 be an OR rather than AND?

 

[TropicalMagic]

Should the 2 conditions for AJ2 be an OR rather than AND?

Thanks for responding! It should be an AND

 

[Fluff]

In that case there is nothing wrong with your formula. Are you sure that there are rows with APAC in both AM & AR and AJ has both OFIA & Intra-Asia in the same cell?

 

[TropicalMagic]

In that case there is nothing wrong with your formula. Are you sure that there are rows with APAC in both AM & AR and AJ has both OFIA & Intra-Asia in the same cell?

Ahhh apologies! What if AJ need Either OFIA OR Intra-Asia in the same cell?

 

[jtakw]

Ahhh apologies! What if AJ need Either OFIA OR Intra-Asia in the same cell?

Hi,

Then, you can use just this:

=--AND(AM2="APAC",AR2="APAC",SUM(COUNTIF(AJ2,{"*OFIA*","*Intra-Asia*"})))

 

[TropicalMagic]

Hi,

Then, you can use just this:

=--AND(AM2="APAC",AR2="APAC",SUM(COUNTIF(AJ2,{"*OFIA*","*Intra-Asia*"})))

Brilliant, thanks!

It was what I was looking for!

I amended it a little for my usage - At Range("BM2") :

=IF(AND(AM2="APAC",AR2="APAC",SUM(COUNTIF(AJ2,{"*OFIA*","*Intra-Asia*"}))),1,0)

 

[jtakw]

Brilliant, thanks!

It was what I was looking for!

I amended it a little for my usage - At Range("BM2") :

=IF(AND(AM2="APAC",AR2="APAC",SUM(COUNTIF(AJ2,{"*OFIA*","*Intra-Asia*"}))),1,0)

You Don't need the IF(...,1,0)

My formula, as posted above:

=--AND(AM2="APAC",AR2="APAC",SUM(COUNTIF(AJ2,{"*OFIA*","*Intra-Asia*"})))

Will produce the 1 and 0 results as you require.