Forum Discussion

TobiLuf's avatar
TobiLuf
Copper Contributor
Feb 14, 2020

Using "No" in Countif formula

Hello all, 

 

I was trying to use a countif formula to find how many instances of "No" appeared in a range. 

i.e =countif(B2:B5, "=no"). Column B contains multiple instances of "No", but the formula keeps returning 0 as a result. I then used the wildcard (*) i.e =countif(B2:B5, "=no*") and then it returned the right figure. Am I doing something wrong? Or does excel not recognize "no" on its own?

excel
Formulas and Functions

8 Replies

  • Abiola1's avatar
    Abiola1
    MVP
    Feb 14, 2020

    TobiLuf 

     

    Hello, the COUNTIFS returned 0 most likely because there are extra spaces. You can use TRIM to remove all the spaces as I did and then the COUNTIFS will deliver the correct values.\

     

     

    • TobiLuf's avatar
      TobiLuf
      Copper Contributor
      Feb 14, 2020

      Abiola1 oh, space in the cell with "No" I was searching for space in the formula. 

      thank you. I've sorted it out. 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 14, 2020

    TobiLuf 

    Most probably you have space or non-printable character after the No. Did you add them manually or copy/paste from somewhere?

    • TobiLuf's avatar
      TobiLuf
      Copper Contributor
      Feb 14, 2020

      SergeiBaklan 

      hi. thanks for your response.

      I just checked, there are no spaces or additional characters after no. its written as (......., "=no")

      I typed the formula in manually. When I delete the * from the formula, it doesn't work. but with the * it works. 

Resources