Calc- How to count selected lines

users
#1

Hi all,

maybe I have a blind spot, but I don't see the solution..

I have a long list (in my case of music numbers, with titles, dates, and admin stuff.

it looks like:

Nr Sub ID Pgs Beg Map Aktie Titel
1
1

Volk'ren, hoort!
2
2

Heer, wees mijn Gids
3
3 1

De waldhoorn
4
4 1

O Heer, die daar (Val.Gedenckklank)
5 A 5A 2

Ei oechajem
5 B 5B 1

Soldatenlied
6
6 2

Die zwölf Räuber
7
7

0 bone Jesu
8
8 1

Nu jubelt en wees blij
9
9 2

Het Gebed (zie 162)
10
10 1

Wat de toekomst brengen moge
11 A 11A 1
19-10 i Stille Nacht
12 A 12A 1
19-10 i Bethlehem
12 B 12B 2

Ik kniel aan Uw kribbe neer
13
13 1
5-01 i Piet Hein (de Zilvervloot)
14
14 2

Sanctus (uit de Deutsche Messe V)
15
15 1 P

Ambrosianischer Lobgesang

Now I have an other tab where I select only things with a value in the "Map" column

1 24-08-15 242 Jacob's Ladder
2 M 275 Dicht bij het hart van God
3 M 279 Geduchte God hoor mijn gebeden
4 M 305 Abba Vader
5 24-08-15 363 Zie de Zon
6 05-01-15 369 Heer mijn hart zoekt u te vinden
7 24-03-14 396 Geef aan de wereld vrede
7 04-04-16 433S Aan de Voorthuizense dreven
8 M 453 Dank zij U Heer

I use the fomula =IF(AND(OR(Lijst.$F2="M",ISNUMBER(Lijst.$F2)),NOT(Lijst.$G2="i")),Lijst.$C2,"") in the third column.
Subsequently I use a filter to just filter anything with a value in the 3rd column.

Question 1:
Is there a more clever way of doing this, so that I don't need the manual action to adapt the filter if something changes in the main list?

Question 2:
I want a sequence number in the first column- I started out with using SUBTOTAL(2,<range>) for this. This didn't work since not all values are numeric (e.g. 433S).
COUNT(D$4:D..) has the same problem as can be seen in above example (twice nr 7)

Any suggestions?
thanks,
Rob.

#2

The table was really messed up- So here it is in text:

ID Pgs Beg Map Aktie Titel
1 Volk'ren, hoort!
2 Heer, wees mijn Gids
3 1 De waldhoorn
4 1 O Heer, die daar (Val.Gedenckklank)
5A 2 Ei oechajem
5B 1 Soldatenlied
6 2 Die zwölf Räuber
7 0 bone Jesu
8 1 Nu jubelt en wees blij
9 2 Het Gebed (zie 162)
10 1 Wat de toekomst brengen moge
11A 1 19-10 i Stille Nacht
12A 1 19-10 i Bethlehem
12B 2 Ik kniel aan Uw kribbe neer
13 1 5-01 i Piet Hein (de Zilvervloot)
14 2 Sanctus (uit de Deutsche Messe V)
15 1 P Ambrosianischer Lobgesang
The second looks like:

Seq Datum Nr Titel
1 24-08-15 242 Jacob's Ladder
2 M 275 Dicht bij het hart van God
3 M 279 Geduchte God hoor mijn gebeden
4 M 305 Abba Vader
5 24-08-15 363 Zie de Zon
6 05-01-15 369 Heer mijn hart zoekt u te vinden
7 24-03-14 396 Geef aan de wereld vrede
7 04-04-16 433S Aan de Voorthuizense dreven
8 M 453 Dank zij U Heer

#3

I'm not sure if this helps with any of your questions but COUNTA counts
numbers and/or text

If you can provide a link to a sample file and explain what you expect maybe
it is easier.

Pedro

#4

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

Thanks for thinking with me,

Rob.

#5

Hi Rob

Rob wrote

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

Thanks for thinking with me,

I really don't understand why it works but it does.
I'm attaching a sample file in case someone finds this useful.
COUNTIF.ods <http://nabble.documentfoundation.org/file/n4179920/COUNTIF.ods>

Thank you for teaching me a new trick. This formula is going to be quite
handy.

Best regards,
Pedro

#6

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.