How to count TRUE entries in a column

[Jonrysh]

A spreadsheet has only the entries TRUE and FALSE.

TRUE
TRUE
TRUE
TRUE
TRUE
FALSE

Which has been set up by entering TRUE in the top position an pulling it down into the rest of the column. To count the number of TRUE entries, which here is 5, the formula =COUNTIF(A1:A6,TRUE) applied to the column should work, but it always returns the result 1. On the other hand, the formula =COUNTIF(A1:A6,FALSE) works correctly on a similar column got by pulling down FALSE.

If a column is set up by actually entering the entries one after another, say

TRUE
FALSE
FALSE
TRUE
TRUE

COUNTIF() works properly. I suspect that the problem is that LibreOffice is entering different kinds of TRUE, because when I apply Data->Filter->Standard_Filter to the first column (all TRUE), I see a large number of entries all TRUE.

What’s going on? Is there any way to set up the column correctly? “Pulling down” TRUE() seems to have its own problems.

[Jonrysh]

Same behavior here as with Horst. BTW: my version of LibreOffice is 3.6.6.2.

[Jonrysh]

Mariosv has a workaround/solution. To see the source of the problem, format the entries in the first column as numeric, i.e. Format_Cell->Number->1234. Then the different kinds of TRUE become visible. This leads to the question: How to pull a constant down a column and not have it change to an increasing sequence of numbers.

[amyabbott090]

I have a spreadsheet that has a section of cells where every cell contains a solitary word. I might want to check the events of certain words. I can utilize the COUNTIF work for most words, yet in the event that the word is “genuine” or “false”, I get 0.

15 Easy And Honest Ways For College Students To Make Money

[mariosv]

Hi Jonrysh,

if there are logical values (0 for false, or 1 for true, or FALSE() / TRUE() functions) formatted as Boolean Value then to count true values, use the formula:
=COUNTIF(A1:A6,TRUE())
or
=COUNTIF(A1:A6,1)

if there are text values then the formula:
=COUNTIF(A1:A6,“TRUE”)

if there are numbers formatted as Boolean Value, remember that all numbers different from zero are showed as TRUE. In this case the formula to count True:
=COUNTIF(A1:A6,"<>0")

[horst]

There is nothing wrong with Jonrysh syntacs. It looks like a bug in the autofill function.

[oweng]

@horst, comment by GerardF in fdo#64001, indicates that the formula =TRUE() should be used to represent a boolean value rather than the string “TRUE”. This certainly avoids the problem reported here and is evidently the only viable internationally workable solution.

[horst]

The culprit in this case is the COUNTIF function. Per definition all values not equal 0 are considered TRUE.

If you type in: =COUNTIF(A1:A6,TRUE) Calc changes the function to =COUNTIF(A1:A6,1) but it should be changed to =COUNTIF(A1:A6,"<>0").

The last formula does the job right.

ADD-ON: Read the Help file it has sometimes the solution. While holding down CTRL-key and pulling down to autofill the original value is not changed.

[horst]

I can conform this strange behavior for 3.6.6 and 4.0.3. If you copy and paste the TRUE everything is ok.

[Jim7fl1]

If the True and False entries are their as a result of boolean entries and are merely displayed as “True” and “False”, you should be able to use a simple sum(a1:a6) the result would be the number of true entries.

You can verify this by formatting your range a1:a6 as Boolean and then using the sum above to test.

Best Regards,

Jim Smith