Countif-function with asterisk in criteria

[Joris Adriaenssens]

I have a list with (alfanumerical) codes in column A. Each code can
occur more than once.

In cel C1 I have one of these codes. In cel D1 I would like to have
the number of occurences of the code in cel C1.

I do this with the formula
=COUNTIF(A:A, C1)

Unfortunately, the code in cel C1 can be something like '*****'
(without the quotes) which is a valid code. When the list of codes
was adopted, nobody thought of the asterisk being a joker-character.

When I put '*****' in cel C1, the formula gives the count of all the
codes in column A, not just the count of the code '*****'.

What can I do to obtain the count of the code '*****' ?

Joris Adriaenssens
Suppose the long list of codes in column A

 

[Don Guillett]

Find/replcace the *** with something else

 

[Dave Peterson]

How about:

=COUNTIF(A:A,SUBSTITUTE(C1,"*","~*"))

 

[Alan Beban]

How about:

=COUNTIF(A:A,SUBSTITUTE(C1,"*","~*"))

Or perhaps simply =COUNTIF(A:A,"~**")

Alan Beban

 

[Dave Peterson]

That would count both ***** and *anytextatall (leading asterisk).

So like most things, it depends on what's in the data.