countif with numerical wildcard

**Dandi** · 09-02-2009, 04:01 PM

I'm an intermediate user so haven't been able to follow some of the answers to similar questions already posted. I have data in a column as follows:

1
1
2
2367
3
8

I want it to output how many times the character "2" appears, in this case, 2. Instead it keeps yielding 1, no matter what I try. The formula I've been working with is:

=COUNTIF(O5:O150,"*"&"2"&"*")

I've tried formatting the data as text or general. I've tried a million variations on whether the 2 and/or asterisks are in quotes, with and without ampersands.

I'm guessing that it's not treating the digits in numbers as text. How do I fix this?

**DonkeyOte** · 09-02-2009, 04:09 PM

If you need to manipulate source you're left with SUMPRODUCT, eg:

Please Login or Register  to view this content.

this assumes 321 should count as 1 ... and 222 also counts only as 1 ... not clear ... if in fact 321 & 222 should yield 4 then:

Please Login or Register  to view this content.

**shg** · 09-02-2009, 04:09 PM

If you want to count all instances,

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10, 2, "") ) )

If you want to count the cells where it appears,

=SUMPRODUCT(--ISNUMBER(FIND(2, A1:A10)))

**Dandi** · 09-02-2009, 04:15 PM

Fantastic, that works. Thank you!

I should have specified: 2 will appear no more than once in a given cell, so the simpler versions you've given work beautifully.

I've never used sumproduct, isnumber, or search before, so will have to check those out.

Thanks very much!!