Countif with star operator in criteria

[mashfour077]

Dear all,

after having searched this group and google-ed and beating my own
brains, I still can't find a (elegant) solution to this small but
rather annoying issue....some help would be appreciated....even when I
am overlooking the obvious.....
Here it is: using COUNTIF to find number of occurrences of a value in a
range in which the value can contain a star operator ( * ).
Example: countif gives this
Column A Column B = Countif($A$4,A2)
FT1305 1
FT1305* 3
FT1305* 3

While I am looking for:
Column A Column B = Function(?????)
FT1305 1
FT1305* 2
FT1305* 2

Any ideas?

Thanks on behforehand,

regards,

Elly

 

[mashfour077]

Oops, typo in the original post:

Example should of course be;
Example: countif gives this
Column A Column B = Countif($A$2:$A$4,A2)
FT1305 1
FT1305* 3
FT1305* 3

While I am looking for:
Column A Column B = Function(?????)
FT1305 1
FT1305* 2
FT1305* 2

 

[Bob Phillips]

Do you mean?

=COUNTIF($A$2:$A$200,A2)

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Ah, I see now.

Try this

=SUMPRODUCT(--(SUBSTITUTE($A$2:$A$4,"*","~")=SUBSTITUTE(A2,"*","~")))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Domenic]

Try...

=COUNTIF($A$2:$A$4,SUBSTITUTE(A2,"*","~*"))

Hope this helps!

 

[mashfour077]

Bob Phillips, Domenic,

both your solutions were the trick ! Thanks a lot for your great help,
even for this rather dumb question, it is much appreciated !

Regards,

Elly

 

[Robert_Steel]

While I am looking for:

Column A Column B = Function(?????)
FT1305 1
FT1305* 2
FT1305* 2

an alternative that I think will be more robust
=SUM(--EXACT(A2,$A$2:$A$4))
note: array entered (CTRL+SHIFT+ENTER)

and copied down

The exact returns an array of True and False
the -- changes them into 1 and 0 that can be summed

hth RES

 

[Bob Phillips]

An alternative, yes, but can you enlighten me as to why this might be more robust?

--

HTH

RP
(remove nothere from the email address if mailing direct)

While I am looking for:
Column A Column B = Function(?????)
FT1305 1
FT1305* 2
FT1305* 2

an alternative that I think will be more robust
=SUM(--EXACT(A2,$A$2:$A$4))
note: array entered (CTRL+SHIFT+ENTER)

and copied down

The exact returns an array of True and False
the -- changes them into 1 and 0 that can be summed

hth RES