COUNTIF not counting unless I specifically type criteria out?

[xtine17]

I am trying to use a COUNTIF function to count how many times names appear throughout the worksheet tabs.  The COUNTIF function works correctly if I physically type out "Smith" for criteria, but not if I use the cell reference with the last name in it?

For Example:

Column B has the reference of last names I am using in my COUNTIF function.  B2 has the first name I am referencing (Smith).  If I type ="COUNTIF('SHEET 1'!A$4:E$20, B2), 0 result populates.  If I type the same formula but as ="COUNTIF('SHEET 1'!A$4:E$20, "Smith"), the correct result populates.

I want to copy this formula down to repeat for over 100 last names... It would take forever to hand type each last name as I copy down the formula.  Any tips?

[#2]

@xtine17 

Most likely B2 doesn't contain "Smith" but "Smith ".

[#3]

Just checked, no spaces in the data reference column. Could it be because out of all the ranges COUNTIF is searching for, this spreadsheet has multiple cells with multiple last names listed (for example 1 cell will say "Smith, Jones, Parker") ?

[#4]

@xtine17 

With this data COUNTIF('SHEET 1'!A$4:E$20, "Smith") would also not count.

[#5]

That's what I assumed but I have tried a few things and if I type =COUNTIF('WEEK 1'!A$4:E$19, "*Smith*"), it is counting correctly. Just not sure why I can't put "*B2*" doesn't output the same? Because I am trying to copy down this formula (with multiple COUNTIF functions added together) down without manually typing a new last name every row.

[#6]

@xtine17 

That would be:

=COUNTIF('WEEK 1'!A$4:E$19, "*"&B2&"*"),

[#7]

@Detlef Lewin Thank you so much.  This worked like a charm and you saved me so much time.  I appreciate it!!!!!