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

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.






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