Ask Your Question
0

How to count TRUE entries in a column [closed]

asked 2013-04-25 22:19:40 +0200

Jonrysh gravatar image

updated 2015-11-14 12:23:48 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-14 12:24:05.737504

Comments

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

Jonrysh gravatar imageJonrysh ( 2013-04-25 23:42:40 +0200 )edit

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.

Jonrysh gravatar imageJonrysh ( 2013-04-26 06:58:47 +0200 )edit

4 Answers

Sort by » oldest newest most voted
1

answered 2013-04-26 22:42:22 +0200

horst gravatar image

updated 2013-04-27 20:04:39 +0200

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.

edit flag offensive delete link more
1

answered 2013-04-25 23:34:11 +0200

m.a.riosv gravatar image

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")

edit flag offensive delete link more

Comments

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

horst gravatar imagehorst ( 2013-04-26 03:40:48 +0200 )edit

@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.

oweng gravatar imageoweng ( 2013-11-18 10:12:23 +0200 )edit
0

answered 2013-04-25 23:15:29 +0200

horst gravatar image

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

edit flag offensive delete link more
0

answered 2013-11-12 15:57:02 +0200

Jim7fl gravatar image

updated 2013-11-12 15:59:58 +0200

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

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-04-25 22:19:40 +0200

Seen: 76,599 times

Last updated: Nov 12 '13