PDA

View Full Version : COUNTIF(FIND())



Sir Babydum GBE
08-07-2006, 06:18 AM
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
08-07-2006, 06:52 AM
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
08-07-2006, 07:03 AM
=COUNTIF(A:A,"*QWERTY*")

Sir Babydum GBE
08-07-2006, 09:31 AM
Thanks both - that's fab!

:)