"*"&$ATU$3&"*" Dilema

[WildWill]

I have the following examples of text entries in a data table:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
G. Marcus-MRC<o></o>
P. Davis-GLTA<o></o>
B. Strauss-MRC<o></o>
<o></o>
The data would typically indicate an individual's Initial and Surname always followed by a "-" (no spaces) and a 3-digit uppercased Mnemonic representing his company's name. I am using the following formula to calculate the totals of cells which relate to a specific company: <o></o>
=COUNTIF($B34:$ATE34,"*"&$ATU$3&"*") where in this example, cell ATU3 would be a holding cell containing (e.g.) "MRC", and then the answer would be returned as "2" for the list provided above. <o></o>
<o></o>
....however, I am getting data errors and things don't seem to add up. Is there a chance that the formula that I am using is simply looking for occurrences of (e.g.) "MRC" in a string of text, and that it will return all those occurrences regardless of capitalisation and/or a prefix of a "-"?<o></o>
<o></o>
I think that perhaps I need a derivative of the formula above which will search SPECIFICALLY for "MRC", but only if it preceded by a "-" with no spaces? For example, if I had "G. Marcus-MRC" an a cell and I entered "ARC" in the holding cell (ATU3 above), then I would a count of "1" because the formula is finding the string "arc" in the word Marcus...whereas what I want to count is all the occurrences of "ARC" only where preceded by a prefix of "-" with no spaces. <o></o>
<o></o>
Is there a way that I can modify the formula to find what I am looking for?

 

[Haseeb Avarakkan]

Hello, May be

=SUMPRODUCT(--ISNUMBER(SEARCH(ATU3,A1:A100)),--ISERROR(SEARCH(ATU3&"* ",A1:A100)))

 

[WildWill]

Hi Haseeb

That does not seem to be the solution - it returns similar number to what I was getting with the original formula - thanks anyway!

 

[Scott Huish]

=countif($b34:$ate34,"*-"&$atu$3)

 

[WildWill]

Thanks HOTPEPPER!!! Sorted.