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

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.

Jay Elston

Posted 2010-05-09T22:48:57.140

Reputation: 983

1Jay, this old question popped up again because of a new post. Embedding an answer in the question sort of messes with the intended structure of the site. Can you move the answer portion to an answer? – fixer1234 – 2015-05-01T20:32:25.287

3For what it's worth I've changed my mind on this. I guess Excel formulae could be considered a kind of declarative programming language. I don't think it is Turing complete without the use of VBA but then neither are regular expressions - which are deemed acceptable by the SO community! – Martin Smith – 2010-05-10T10:19:40.237

1Stick with the wildcard solution. =COUNTIF(A1:A5,"<truf")-COUNTIF(A1:A5,"<=trud") counts every string that begins with “true” or “trud”, such as “trudge”. – Scott – 2013-05-29T23:26:08.197

Answers

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.

Sux2Lose

Posted 2010-05-09T22:48:57.140

Reputation: 2 962

1Good suggestion -- I had not thought about wild cards. A second COUNTIF can be used to filter out words that happen to end with "true". This formula works:

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

I would upvote this answer, but I am a newbie will only 11 rep so far. (I can't transfer any from stackoverflow :-( – Jay Elston – 2010-05-13T00:23:25.950

1Here’s another variation: =COUNTIFS(A1:A5, "tru?", A1:A5, "*e"). – Scott – 2013-05-31T23:45:01.053

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

Christian Garbin

Posted 2010-05-09T22:48:57.140

Reputation: 171

My temporary workaround went something like that -- I inserted another column and used the formula: IF(A1="true","T",IF(A1="false","F","")) to populate the cells. – None – 2010-05-09T23:26:47.473

Hmm I can see the frustration. COUNTIF(A2,">=trud") and COUNTIF(A2,"<=truf") both work. COUNTIF(A2,A2) and any conceivable variant doesn't! – Martin Smith – 2010-05-10T00:16:05.340

It seems that =COUNTIF(G1,">=true") and =COUNTIF(G1,"<=true") always return 0 for any possible input from CHAR(1) to CHAR(255) so whatever it is being converted to seems completely incomparable. It seems to be acting like a NULL comparison in a database would. – Martin Smith – 2010-05-10T10:50:37.523

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.

Jay Elston

Posted 2010-05-09T22:48:57.140

Reputation: 983

I was able to use the following formulas:

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

Read through the built-in help for COUNTIF. They describe the ability to use operators on the condition in more detail.

technomalogical

Posted 2010-05-09T22:48:57.140

Reputation: 665

1Doesn't work for me. Returns 0. – Martin Smith – 2010-05-10T18:23:17.347

1This does not work for me either. – Jay Elston – 2010-05-13T00:24:36.493

I had a similar issue with a SUMPRODUCT formula. By removing the quotes around the term FALSE the formula produced the correct result, using MS EXCEL 2003.

rohan

Posted 2010-05-09T22:48:57.140

Reputation: 121

1I mentioned in the question that I tried =COUNTIF(A1:A5,TRUE), and that I was using the 2007 version of Excel. – Jay Elston – 2012-04-30T20:40:51.787

I was able to resolve the problem using the following formula:

=COUNTIF(A1:A5,"T*").

"T*" replaces "True" if the entries in the column are limited to only one "T" word.

Rocky Ellens

Posted 2010-05-09T22:48:57.140

Reputation: 1