Excel – Using excel, how can I count the number of cells in a column containing the text “true” or “false”

microsoft excelspreadsheetworksheet-function

I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the word is "true" or "false", I get 0.

        A        B
1    apples      2
2    true        0
3    false       0
4    oranges     1
5    apples

In the above spreadsheet table, I have these formulas in cells B1, B2, B3 and B4:

=COUNTIF(A1:A5,"apples")
=COUNTIF(A1:A5,"true")
=COUNTIF(A1:A5,"false")
=COUNTIF(A1:A5,"oranges)

As you can see, I can count apples and oranges, but not true or false. I have also tried this:

=COUNTIF(A1:A5,TRUE)

But that does not work either.

Note — I am using Excel 2007.

Best Answer

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.

Related Question