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.

 

[RLJ]

Try =COUNTIF(A1:A1000,B1) where B1 is your cell reference to the text string you are looking for.

 

[Aladin Akyurek]

Try...

=COUNTIF($A$1:$A$1000,"*" & B1 & "*")

where B1 houses an item of interest like nav.

 

[Fishless]

Thank you both for your quick responses.

B1 has the reference I'm looking and I can get that to work, but what I need to match is.. lets say B1 has the name Rodger Clemings. But what I need the countif function to do is go to B1 and just take perhapes "odger Clem" as the string.

I know this is a stretch but I know somehow it can be done. Is excel smart enough to look at B1 and match 8 out of 15 or so characters? I guess the better question is am I smart enough to make it do that.

 

[Aladin Akyurek]

Thank you both for your quick responses.

B1 has the reference I'm looking and I can get that to work, but what I need to match is.. lets say B1 has the name Rodger Clemings. But what I need the countif function to do is go to B1 and just take perhapes "odger Clem" as the string.

I know this is a stretch but I know somehow it can be done. Is excel smart enough to look at B1 and match 8 out of 15 or so characters? I guess the better question is am I smart enough to make it do that.

Not sure I understand the query, but here a guess:

=COUNTIF($A$1:$A$1000,RIGHT(B1,8))

where RIGHT takes last 8 chars of the value in B1 and counts literal
occurrences of this in $A$1:$A$1000.

 

[Fishless]

That looks like the one. I'll post back here in a sec. Thanks!

 

[Fishless]

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.

 

[barry houdini]

So what's a valid match in column A if B1 = "Rodger Clemings"?

This formula will count all column A values that are any 8 consecutive characters from B1, e.g. "Rodger C" through to "Clemings" by way of "ger Clem", "r Clemin" etc.

=SUMPRODUCT(ISNUMBER(MATCH(A1:A1000,MID(B1,ROW(INDIRECT("1:"&LEN(B1)-7)),8),0))+0)

it's not case-sensitive

 

[mikerickson]

Might there be a problem with trailing spaces?

 

[T. Valko]

Thank you both for your quick responses.

B1 has the reference I'm looking and I can get that to work, but what I need to match is.. lets say B1 has the name Rodger Clemings. But what I need the countif function to do is go to B1 and just take perhapes "odger Clem" as the string.

I know this is a stretch but I know somehow it can be done. Is excel smart enough to look at B1 and match 8 out of 15 or so characters? I guess the better question is am I smart enough to make it do that.

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.

If B1 contains Rodger Clemings and you only want to search for odger Clem (which doesn't make any sense to me!)...

=COUNTIF(A1:A100,MID(B1,2,10))

Or, possibly:

=COUNTIF(A1:A100,"*"&MID(B1,2,10)&"*")