Re: [libreoffice-users] Calc- How to count selected lines

Rob Jasper wrote:
> Pedro,
>
> In the 'derived' view in the second tab (example 2) the empty fields under heading Nr do contain a 
> formula to display the number only if something is specified in the 'map' column in the main list 
> (example 1).
> COUNTA also counts cells when they only contain a formula that returns an empty string. So, the 
> effect is that it will count all cells.
> COUNT only counts cells containing numeric values
> I couldn't find how to use COUNTIF to do what I want. I always seem to have troibles with the 
> formatting...
> I tried:
> COUNTIF(D$4:D4,"<> ")
> COUNTIF(D$4:D4,'<>" "')    ##   
> COUNTIF)D$4:D4,"<>''")     ## string delimited with single quotes, inside total condition withing 
> double quotes
>
> I just found 2 formats that seem to work:
> COUNTIF(D$4:D4,"<>"&"")
>
> and also
> COUNTIF(D$4:D4,"<>")
> No clue why this works, but it does...

You can use expressions like COUNTIF(D$4:D4,">2") to count cells with a 
value greater than 2.

There are two main conventions I've come across in programming languages 
for "not equals" - "<>" or "!=". COUNTIF seems to use "<>" - you can use 
an expression like COUNTIF(D$4:D4,"<>2") to count cells with a value not 
equal to 2, or COUNTIF(D$4:D4,"<>Hello") to count cells with a value not 
equal to "Hello".

I guess COUNTIF(D$4:D4,"<>") counts all cells not equal to blank.

I'd never thought of that. In the past, I've used COUNTIF(D$4:D4,".+"), 
which requires Tools > Options > Calc > Calculate > "Enable regular 
expressions in formulae" to be enabled. ".+" is a regular expression 
matching cells which contain one or more characters. It looks like I've 
been overlooking an easier (and probably efficient) solution, so thanks 
for mentioning that.

--
Mark.


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted