Using Countif function for string using a cell reference

[Fishless]

First post, hello fellow Excel Geeks

My question is related to the following formula:
=COUNTIF(A1:A1000,"*" & "nav" & "*")

My question: Can this formula be modified so that I can use a cell reference instead of an exact string? Actually, I want to do both. I want to be able to use the cell as the reference that I pull a string from.. Hope that makes sense. That way I can copy the formula easily into many cells. Without modifying it for an exact string.

The reason I need this is because my employees manually enter other employee's name in a column. I then match their entry to my entry. They have a habit of misspelling names, misformatting, and putting spaces where they dont' need to be which is why I want the countif function to match as bestly as it can and ignore some of there mistakes.

Thanks in advance! Again, this is my first post so be easy.

 

[Aladin Akyurek]

I can't get that one to work. I even changed the numbers of characters all the way down to 3 and still couldn't get it. Weird. Thanks anyway's Aladin.

Care to post a small sample along with the item you want to count?

 

[Fishless]

Ok, I apologize if I haven't been specific to why I need such an unusual formula.

The reason is because employess enter the information manually (I can't change that). They often put in erroneous spaces and also misspell names.

So my goal was to have a countif formula that would count something like: (A1-A5000) Samuell Clemmings or Samuel Clemmings (employee entrys). When the actual name in my column would be Samuel Clemmings-B1.

Hope that helps guys. I'm going to try out Barry's formula and Valko's but I suspect from the limited knowledge I have that these will work until a silly employee enters an erroneous space.

 

[Fishless]

Yes Aladin:

Column A1:A50000 My column B1:B5000

Julie1 Bartlick Julie Bartlick
Julie Bartlick Samuel Clemmings
sameul clemmings

Sorry If I'm giving you guys a hard time... my employees are rubbing off on me lol.

 

[Fishless]

That didn't come out right.

Column A1:A50000 (Employee entrys) - Julie1 Bartlick; Julie Bartlick (extra spaces between Julie and Bartlick; Sameul Clemings

My column entrys (Spelled and spaced right) B1:B5000 - Julie Bartlick; Samuel Clemings

 

[T. Valko]

Ok, I apologize if I haven't been specific to why I need such an unusual formula.

The reason is because employess enter the information manually (I can't change that). They often put in erroneous spaces and also misspell names.

So my goal was to have a countif formula that would count something like: (A1-A5000) Samuell Clemmings or Samuel Clemmings (employee entrys). When the actual name in my column would be Samuel Clemmings-B1.

Hope that helps guys. I'm going to try out Barry's formula and Valko's but I suspect from the limited knowledge I have that these will work until a silly employee enters an erroneous space.

Maybe you could use a data validation list with the employee names?

That way, all they do is select their name from a drop down list. No chance of spelling errors that way.

 

[Fishless]

I wish Valko .

Believe me I would love to impliment that, but this is a call center and the names are not even restricted to this site but four sites at least with probably 4000 different names. And I couldn't get the list even if I wanted.

 

[T. Valko]

I wish Valko .

Believe me I would love to impliment that, but this is a call center and the names are not even restricted to this site but four sites at least with probably 4000 different names. And I couldn't get the list even if I wanted.

Ok, well, with all the different ways to misspell a word I think you're going to find this very difficult, if not impossible, to do.

Good luck!

 

[Fishless]

I think you are right, but I wanted to thank everyone for trying!

 

[Aladin Akyurek]

Yes Aladin:

Column A1:A50000 My column B1:B5000

Julie1 Bartlick Julie Bartlick
Julie Bartlick Samuel Clemmings
sameul clemmings

Sorry If I'm giving you guys a hard time... my employees are rubbing off on me lol.

You might want to have a look at:

http://www.mrexcel.com/forum/showthread.php?t=195635

 

[Fishless]

Very interesting! Thanks Aladin. I just now used another formula from a post you resonded to a couple years ago to return a row result by a matched cell. Thanks for your contributions!