What's Wrong With This "WhatIf"?

[Cyberdude]

I have a column of text values that are either "TRUE" or "FALSE". I want to count the ones that are "TRUE".
I use the formula:
=CountIf(A1:A3,"TRUE")
For the test I have A1 and A3 blank. The result of the CountIf is always 0. The cell that contains "TRUE" contains a formula that sets the cell value to the text value "TRUE". I also tested it by removing that formula, then directly typing "TRUE" (w/o quotes) into the cell. Still get 0. The cell is formated as "General". I also removed the quotes from the "TRUE" in the CountIf statement. Still get 0. It suggests that somehow the text value TRUE is being treated as a boolean value, but I would think that putting quotes around it would prevent that confusion. What am I missing?? :banghead:

[Jacob Hilderbrand]

TRUE and FALSE values in a cell can get you some off results. What you should do it:


=COUNTIF(A1:A3,TRUE)

Then have A1:A3 return either TRUE or FALSE without quotes.

[TheAntiGates]

The answer would seem to be that on this issue Excel is insane, not you :*) . You can also go

=IF(A1,1,0)
to demonstrate that ="TRUE" is boolean.

I would assume that this behavior was chosen because external databases often contain strings like "FALSE," so Microsoft decided to hardwire the recognition as a Boolean. That was nice of them wasn't it? :banghead: