Using excel, how can I count the number of cells in a column containing the text "true" or "false"?

The second argument to the COUNTIF formula is interpreted by Excel as a test to be performed (e.g. we can enter ">3", etc.). In this case, it looks like Excel is converting the "true" and "false" strings to 1 and 0. That won't match the original text.

The only solution I know to this problem is to write VBA code to do the counting.

If changing the input data is acceptable, replace "true" with "T" and "false" with "F", then change to COUNTIF(A1:A5,"T").

When I tripped over this, I gave up the battle and changed the input data.

P.S.: Using Excel 2003 - same issue

This should work:

=COUNTIF(A1:A5,"*true")

although it will count a cell if it has any text prior to true as well. But it may be a crude workaround for you.

It appears that Excel treats "true/TRUE" and "false/FALSE" as magic strings when they appear in formulas -- they are treated as functions: TRUE() and FALSE() instead of strings.

If you need to count true and false in a column, you will not get a count if you use "true" or "=true" as the criteria. There are some approaches you can use to count true and false as words.

Use a pair of COUNTIF functions. Either of the following works:

=COUNTIF(A1:A5,"*true")-COUNTIF(A1:A5,"*?true")

or

=COUNTIF(A1:A5,"<truf")-COUNTIF(A1:A5,"<=trud")

Create a new column with true converted to "T" and false converted to "F" using the formula:

=IF(A1="true","T",IF(A1="false","F",""))

Then trues and falses can be counted using:

=COUNTIF(A1:A5,"T")
=COUNTIF(A1:A5,"F")

Don't use "true" and "false" to begin with, use something else (such as T and F).

Thanks to Sux2Lose for the wildcard idea and Martin Smith for the idea to use comparisons with the strings immediately greater and less than true or false.