Yep. It occurs in =sumif(), =countif(), =match(), =vlookup(), =search() (but
not =find() for some strange reason???). And maybe more(???).

Excel supports wild cards:
* = any string of characters
? = any one character

and the way you tell excel that you want to use an asterisk is to prefix it with
~ (~*).

And since that ~ is a special character, you have to tell excel to use two when
you want to use 1. ~ becomes ~~.

So this could be an =vlookup() formula that "fixes" all 3 of those special
characters:

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"),
Sheet2!$A:$B,2,FALSE)

So...

In your formula, you could use:
=COUNTIF($I$5:$I$13,SUBSTITUTE(I5,"*","~*"))

But if there's a chance that any of those strings contain *, ? or ~, you'd want
to use:

=COUNTIF($I$5:$I$13,
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I5,"~","~~"),"?", "~?"),"*","~*"))

(all one cell)

Jerry wrote:

I have had an inconsistent problem using Countif (Excel 2003) to find
duplicates in a column. I do not wish to remove them just count the number
of times the item shows in the list.

The data column looks like this.

ID # Countif (Duplicate)
BW-CA-46**94-2227626 1
BW-CA-4614-*94-2227626 1
BW-CA-4**94-0358460 3
BW-CA-4213**94-1009696 1
BW-CA-4258**94-1009696 1
BW-CA-4505**94-0358460 1
BW-CA-4808--94-0358460 1

The Countif uses this =COUNTIF($I$5:$I$13,I5)
The first item also counts the second and the third counts the last two. To
confuse me the 4th and fith do not count each other. When I replace the **
with -- I get the correct result. Any Idea why the * or ** cause this
problem and does it occur in other functions.
--
Jerry

--

Dave Peterson