Thread: COUNTIF with partial match?

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?

=COUNTIF(range,"Example*")

assuming you don't want to find

testing Example testing

if you do:

=COUNTIF(range,"*Example*")

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)

Try this it will count TEXT and NUMBERS

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