Countif with star operator in criteria

M

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
 
M

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
 
B

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)
 
B

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)
 
M

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
 
R

Robert_Steel

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

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
 
B

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
Click to expand...

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
 
Top