Countif with two text (string) criteria

[Hollywoood]

Hi all, I am sure this is very simple but I just do not seem to be returning the correct result. I am trying to count the occurences with two separate and independent strings of text both exist in a worksheet.

I have been trying variations of the following with no success

IF(A2<>"",COUNTIF('Sheet1'!M:M,AND("*"&A2&"*","*"&"STRING TWO"&"*")),"")

In english my intent is to look at column M:M of Sheet1 and count the instances, where A2 is non non null, the text in cell A2 as well as the specified text for string 2 both exist in a given cell. I believe my problem is in the underlined area. I have a similar formula that works perfect without the AND clause and second string ie) =IF(A2<>"",COUNTIF('Sheet1'!M:M,"*"&A2&"*"),"")

As always thanks for your time and help

 

[Scott Huish]

Is the second string always after the string from A2?

=IF(A2="","",COUNTIF(M:M,"*"&A2&"*STRING TWO*"))

 

[Jonmo1]

Would STRING TWO come AFTER A2 within a cell in column M ?

If so, you could do

=IF(A2<>"",COUNTIF('Sheet1'!M:M,"*"&A2&"*"&"STRING TWO"&"*"),"")

 

[Excelestial]

Have you tried using COUNTIFS...which allows you to count mulitple criteria?

 

[Hollywoood]

Unfortunately no, there is not set rule for the population of the cell in terms of either limited contents or order. I've tried to think if it could be ordered this way but it really cant.

The cell contents of which I am searching could include either item or neither or both along with several others.

The second string however will always be the same string if that in anyway helps.

 

[Hollywoood]

Excelestial - unfortunately I only have 2003 and thus no countifs option

 

[Excelestial]

Use SUMPRODUCT and convert to boolean values so that the number of "true" conditions are counted. Enter as an array (CRTL+SHIFT+ENTER)

 

[Aladin Akyurek]

If the problem is not yet resolved, try to produce some example items from column M which meets the conditions.

 

[Hollywoood]

Thanks for pointing me back to that route Excelestial I'd tried that as well but I was using a nested AND condition that I didnt need to be and in my own blunder had an incorrect cell reference... so when I was getting the wrong answer it wasnt the method but the human

=SUMPRODUCT(--(ISNUMBER(FIND(A2,'Master'!$M$1:$M$500))),--(ISNUMBER(FIND("String 2",'Master'!$M$1:$M$500))))

 

[Hollywoood]

Sorry I meant also to reference the code in the above post as that which ended up solving my issue in case others wanted to leverage it down the road or are having the same issue

Thanks as always all