Feb 14 2020 05:45 AM
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?
Feb 14 2020 05:50 AM
Most probably you have space or non-printable character after the No. Did you add them manually or copy/paste from somewhere?
Feb 14 2020 06:07 AM
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.
Feb 14 2020 06:31 AM
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.\
Feb 14 2020 06:38 AM
@Abiola1 oh, space in the cell with "No" I was searching for space in the formula.
thank you. I've sorted it out.
Feb 14 2020 06:39 AM - edited Feb 14 2020 06:40 AM
update: I now understand what you mean by space. I have sorted it out. thank you.
Feb 14 2020 06:41 AM
You may always check by =LEN(cell). If more than 2 you have extra characters.