View original (French)

Calc: Number of lines in a filter

Solved
Aranud87 Posted messages 277 Registration date   Status Contributeur Last intervention   -  
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   -
Hi,

I can't figure out how to easily find the number of rows after filtering.

For example, in my hotel, I want to know all the departures today.
So I take a week from 20/03 to 27/03.

I have about 5,000 rows, so I "auto-filter" my first row and restrict it to departures on 27/03/2014.

There I only have all my departures from 22/03, but how can I get the number of rows?
In Excel, it shows me at the bottom left, like: "125 records out of 5300."

Any ideas?
Thanks

--
Simple and community-based
GNU/Linux Mageia :)

1 réponse

Réponse 1 / 1
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
Hello
to get the count of departures on 27/03 without any filter, assuming that all rows have a date
to adapt
in column A from row 2 to 5000, the dates and in B1 the date sought
in C1 the formula
=COUNTIF(A2:A5000,B1)
will give you the number of rows with the date in B1 in A
and therefore the number of filtered rows

best regards

To err is human, to persist is diabolical
1
Aranud87 Posted messages 277 Registration date   Status Contributeur Last intervention   3 299
 
Thank you for your feedback, I'll try that :)
0
Aranud87 Posted messages 277 Registration date   Status Contributeur Last intervention   3 299
 
It works, thank you, even if it's not the simplest.

Just for your information, you can enter the value like this:
=COUNTIF(Q2:Q1341;"19/03/2014")
0
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
So to directly get the number of filtered rows, you can use an out-of-field column where you place the formula, for example in column Z
=IF(A1="",1,0)
and drag it down the height of the field
it will display 1 on each row where you place any value in A
Then, above the filtered line, you place this formula

=SUBTOTAL(9;Z10:Z5000)

which will give you the number of visible 1s after filtering

cheers
0