Using "No" in Countif formula

[TobiLuf]

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?

[#2]

@TobiLuf 

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

[#3]

@Sergei Baklan 

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. 

[#4]

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

[#5]

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

thank you. I've sorted it out. 

[#6]

@Sergei Baklan 

 update: I now understand what you mean by space. I have sorted it out. thank you. 

[#7]

@TobiLuf 

You may always check by =LEN(cell). If more than 2 you have extra characters.

[#8]

@TobiLuf , you are welcome

[#9]

You're welcome