COUNTIF not counting unless I specifically type criteria out?

Question

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?

detlef_lewin · Answer

xtine17&nbsp;Most likely B2 doesn't contain "Smith" but "Smith ".&nbsp;

xtine17 · Answer

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") ?

detlef_lewin · Answer

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

xtine17 · Answer

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.

detlef_lewin · Answer

xtine17&nbsp;That would be:=COUNTIF('WEEK 1'!A$4:E$19, "*"&amp;B2&amp;"*"),&nbsp;

Forum Discussion

COUNTIF not counting unless I specifically type criteria out?

6 Replies

Resources