COUNTIF(FIND())

[Sir Babydum GBE]

Hi,

I want to count cells within a range that contain, say, "QWERTY" anywhere within the cell string.

So if A1 contains "ASDQWERTY", A2 contains "PIYTREWQ" and A3 contains "SQWERTYMM", the count formula will return a 2 on the range A1:A3

Is this possible?

Thanks in advance.

BD

[LitVilkas]

Enter the string you search for in B1

The formula you need is:
{=COUNT(SEARCH(B1;A1:A100))}

{} indicate that this a matrix (or array) formula. Don't enter these brackets manually, but write / paste the formula =COUNT(SEARCH(B1;A1:A100)) and then finish with Ctrl+Shift+Enter

Formula comes from the heroes at www.excelformeln.de (German only, I'm afraid - write them a thank you anyway!)

[Bob Phillips]

=COUNTIF(A:A,"*QWERTY*")

[Sir Babydum GBE]

Thanks both - that's fab!