Countif with star operator in criteria
 

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

Countif with star operator in criteria
 

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

Countif with star operator in criteria
 

Do you mean?

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

and copy down

--

HTH

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


wrote in message
oups.com...

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

Countif with star operator in criteria
 

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)


wrote in message
ups.com...
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

Countif with star operator in criteria
 

Try...

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

Hope this helps!

In article .com,
wrote:

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

Countif with star operator in criteria
 

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

Countif with star operator in criteria
 

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

Countif with star operator in criteria
 

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)


wrote in message ...

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