Using some kind of LIKE statement within CountIf

[TUSSFC]

Here's the scenario ...

I have a column of data (no of rows will change) and I need to know how many records contain a specific phrase within the cell value.

The whole cell value will not match the specific phrase but it will contain it.

e.g. How many cells in Column E contain the phrase "SPECIAL REQUIREMENT"? An example of one of the cell values could be:

E2 = "THIS IS A SPECIAL REQUIREMENT. Refer to case reference 12345."

Any ideas??????

 

[chergh]

try:

=countif(E1:E10, "*SPECIAL REQUIREMENT*")

 

[TUSSFC]

Spot on! Thanks

 

[TUSSFC]

To adapt this further ... is there a way to add multiple criteria to this statement, referring to a different column?

So I'd like to know how many records have "SPECIAL REQUIREMENT" in column E (as above) AND the statement "Commissioned" in column D.

I've tried:

=COUNT(IF(E2:E50000="*SPECIAL NEEDS*"),IF(D2:50000="Commissioned"),E2:E50000)

But I'm getting syntax errors.

 

[chergh]

Try this

=sumproduct((ISNUMBER(FIND("SPECIAL NEEDS",E2:E50000)))*(ISNUMBER(FIND("Commissioned",D2:D50000))))

 

[qafself]

If you use search ather than find, it has the advantage of not being case sensitive

=SUMPRODUCT(--(ISNUMBER(SEARCH("special needs",E2:E5000))),--(D2:D5000="Commissioned"))

For a full explanation of SUMPRODUCT see

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Ed

 

[chergh]

Thanks, always found the case sensitivity to be annoying.

 

[qafself]

No problem - you beat me to the draw on the response though!

Ed

 

[TUSSFC]

Thanks Chaps

 

[sueG]

I tried using the above answer for my prob but it didn't work. I need to find the total number of all the cells in a column with 'sold' in and all the cells in a different column with 'new' in. both of these must be true. I usually use the countif function but is doesn't seem to work when you are looking for two arguments to be true?? Any ideas??

 

[qafself]

Similar formula should work - can you post an example or at least give some details of the ranges where the values may occur.

Edit

If you only have the word sold in one column and the word new in the other one then

=SUMPRODUCT(--(your_range here="sold)*(your_range here="new"))

Note: You can't use full columns e.g.A:A and both ranges must have the same start and finish row