11

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
  • 1,471
  • 3
  • 13
  • 25

7 Answers7

8

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
  • 181
  • 2
6

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
  • 3,367
3

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
  • 1,471
  • 3
  • 13
  • 25
0

If the column does not contain numbers formatted as numbers then this will work and can be copied / pasted down next to your data as needed:

=IF(ISERROR(NOT(A1)),COUNTIF(A:A,A1),SUM(IFERROR(1*NOT(IF(A1,NOT(A:A),A:A&"")),0)))

The explanation is messy because the solution is messy.

NOT(A1) returns an error for strings except the magic true and false as other answers mentioned.
If that returns an error, then it's a regular string and we can use a regular COUNTIF().
If there isn't an error, then it's either a number or true / false.
This is where it gets messy.

... SUM(IFERROR(1*NOT(IF(A1,NOT(A:A),A:A&"")),0)) ...

SUM(IFERROR(1*NOT(~),0)) will create an array of 1 and 0 then sum the array. If NOT(~) is false or an error, the array is 0 at that location. Only if NOT(~) is true will it return 1. So let's break down that ~. If we've gotten this far in the formula, we know the value in A1 is either true or false (or a number, which is why it's important your data not have numbers formatted as numbers).

  • If A1 is true, we want NOT(A:A) because that will turn all the true values into false values which will be inverted again by the NOT(~) that it's wrapped in.
  • If A1 is false, we want A:A&"" because, without the &"", we'll end up counting all the blank cells, too. (NOT([blank]) = TRUE but NOT("") = #VALUE! because it's a string.)
Engineer Toast
  • 5,218
0

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
  • 121
0

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.

jonsca
  • 4,084
Rocky Ellens
  • 1
-1

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
  • 733