Forum Discussion

Copper Contributor

Mar 29, 2023

COUNTIF not counting unless I specifically type criteria out?

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 ...

excel

Detlef_Lewin

Silver Contributor

Mar 29, 2023

xtine17

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

xtine17

Copper Contributor

Mar 29, 2023

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
Silver Contributor
Mar 29, 2023
xtine17
With this data COUNTIF('SHEET 1'!A$4:E$20, "Smith") would also not count.
- xtine17
  Copper Contributor
  Mar 29, 2023
  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
    Silver Contributor
    Mar 29, 2023
    xtine17
    That would be:
    =COUNTIF('WEEK 1'!A$4:E$19, "*"&B2&"*"),