COUNTIF with partial match?

**kaydee** · 11-03-2008, 08:59 AM

Hi

I have a column with a different alphanumeric strings in each cell. Some of these values have a portion that is common. For e.g.:

Example kealey 1980
Testing
Reference
Example john commission
Example (hard sell)

Is there a formula to return the number of cells that have "Example" in them?

**DonkeyOte** · 11-03-2008, 09:01 AM

=COUNTIF(range,"Example*")

assuming you don't want to find

testing Example testing

if you do:

=COUNTIF(range,"*Example*")

**kaydee** · 11-03-2008, 09:16 AM

Thank you, but I already tried this. Sorry - I think my example was not accurate.

Actually the common factor here is a number (1980) at the beginning. When applied, this formula does not count cells which only have "1980". So, for

1980 kealey
Testing
Reference
1980 john commission
1980
1980 (hard sell)

it returns a count of 3 while I want it to be 4.

Sorry for confusion

**DonkeyOte** · 11-03-2008, 09:18 AM

Given mixed data types (text and numerics) you could use something like:

=SUMPRODUCT(--(ISNUMBER(SEARCH("1980",A1:A6))))

or combine 2 Countif's

=COUNTIF(A1:A6,"*1980*")+COUNTIF(A1:A6,1980)

**oldchippy** · 11-03-2008, 09:20 AM

Try this it will count TEXT and NUMBERS

=COUNTIF(A1:A6,"*1980*")+COUNTIF(A1:A6,1980)

**kaydee** · 11-03-2008, 10:07 AM

Both worked but I used the SUMPRODUCT function!

Thanks to both of you for taking the time to respond, and sorry for not being clear enough in the first post.