countif not working when trying to find the word "true"

[Berrett]

I have a formula that looks like this:

=C2&" ("&COUNTIF(C4:C212,"true")&")"

However the countif formula is returning zero no matter how many times the word "true" occurs in the references cells. This has something to do with the word "true" itself because if I change the word to something else (like "donkey") then it will count appropriately.

However, I have 24 sheets of data that all have the word "true" in relevant cells. I would rather not find/replace all of the "true" values and exchange them for some other word. Also, the word true has face value in this particular project.

Does anyone know why the formula breaks when it searches for that word?

-- Other info: I'm using data that has been exported from SPSS's text analysis software. I can't see any evidence that the cells have formatted in any weird way, but I thought it was worth mentioning.

 

[jmthompson]

have you tried ""true""

 

[njimack]

Is it possible they contain leading/trailing spaces? Try:
=COUNTIF(C4:C212,"*true*")

Alternatively, you could TRIM the cell in C4:C212 first

 

[Berrett]

Thanks Jen - it said that the formula I typed contained an error.

Just to clarify: The values in the cells of the worksheet containe the word "true", no quotation marks.

 

[Berrett]

Neil - That did it! Thank you. I checked for leading and trailing spaces and didn't see any, so honestly, I'm not sure why it's working now. But it is.

Thank you for your help.

 

[VoG]

If it is a Boolean True try

=C2&" ("&COUNTIF(C4:C212,TRUE)&")"

 

[njimack]

Neil - That did it! Thank you. I checked for leading and trailing spaces and didn't see any, so honestly, I'm not sure why it's working now. But it is.

Thank you for your help.

Glad it worked. You can check for leading/trailing spaces by testing the length, i.e =LEN(A1)