Forum Discussion

Silver Contributor

Re: COUNTIF not counting unless I specifically type criteria out?

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

5 Replies

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.

Resources