Created attachment 121151 [details] Test file for cell count Hi, In attached file there are a few strange things about counting of filtered cells. The file contains two sheets "Logg" and "Count". On sheet "Logg" there is a list (named area "LoggDB") with one column "Typ". The list if pre-filtered for "Ozone McDaddy". Strange things are that; 1. On sheet "Logg", the number of displayed rows containing "Ozone McDaddy" are 20. But when selecting the same cells, the status bar displays a row count of 51! On sheet "Count" function DCOUNTA is used to count number of rows containing for "Ozone McDaddy" in cell N4; the result is (correctly) 20. 2. On sheet "Count" function DCOUNTA is also used to count number of rows containing for "UP Summit XC2" in cell M4; the result is 174. Filtering the list on sheet "Logg" and selecting all filtered cells yields row count of 194! I expected the result from DCOUNT and the status bar row count for selected cells to match. That is not the case. I'm using Linux Debian stable (jessie) release of LibreOffice; Version: 4.3.3.2 Build ID: 430m0(Build:2) What could be wrong?
Hi @snoozerman, thanks for reporting. I can not reproduce the issue: Win10x64 Version: 4.3.7.2 Build ID: 8a35821d8636a03b8bf4e15b48f59794652c68ba Version: 5.0.4.1 (x64) Build ID: 2def61bcbb29a7a8611b833682fe1291910b11ad
Created attachment 121177 [details] Inconsistency with status bar row count
Created attachment 121178 [details] Inconsistency between statusbar and DCOUNTA()
Hi, I can reproduce it on Windows 7 x64 with LibreOffice Calc; Version: 5.0.2.2 (x64) Build ID: 37b43f919e4de5eeaca9b9755ed688758a8251fe I attach two screenshots to show the effect. Those a are from my Linux system but the result is identical on the Windows PC with LibreOffice 5. I now see that rows hidden by the filter on sheet "Logg" are actually included in the row count shown in the status bar. If I select all filtered cells containing "UP Summit XC2", status bar claims 194 rows are selected. If these are then copy/pasted into a new worksheet and selected, status bar reports 174 (which is same as reported by DCOUNTA). In other words, this bug have probably nothing to do with DCOUNTA(). It is about the status bar selected rows count. Sure it can't be reproduced?
Created attachment 121182 [details] Screenshot I'm not able to reproduce. For me selecting "UP Summit XC2" status bar shows 174 for COUNTA.
Hi, You mean status bar shows "Sum=174" if you select cell M4 (which contains DCOUNTA) on sheet "Count". But if you select all filtered cells (starting with A425 and ending with A618) on sheet "Logg" the status bar shows "Selected 194 rows, 1 columns", doesn't it?
I wasn't looking for "## rows, ## columns selected", this return how many cells are in the selected area but it has not account of the rows/columns are filtered or hidden. At the right of the status bar there is a function box where you can select which function for calculate on the selected cells, like SUM COUNT COUNTA and other. This has in account filtered cells.
Oh I see. Nonetheless, I must say that's a quite unexpected behavior because the selection does in fact *not* include rows filtered out. This is obvious if you copy the filtered rows and paste them on a new worksheet - only filtered/displayed cells are copied. Not the hidden ones in between. Moreover, in the status bar box mentioned, you can select "Selection count" which do report count of visual cells only. The wording "selection" is the same but the result is different. That said, I would classify this issue as a wording error then. The text "Selected xx rows, xx cells" should be altered. Or, preferably, the status bar is changed to display the "real" row count of the selection. Would be more useful. From here, I will leave it to developers to change status to whatever is considered appropriate. Thanks for the quick response!
I also think this is an inconsistency, but not sure how it could be resolved to always be straightforward. Normally selecting a range includes all elements in the range (so row 554-604 is indeed 51 rows). And logically they're still selected, even if some of the rows are hidden (that's why if you select these rows, then right click the hidden part and click Show Rows, the additional rows will be included in the selection). Why they aren't copied... probably because of practical reasons. An alternative way of selection is holding Ctrl and clicking individual fields, in this case if the selection is not continuous, the text in the status bar disappears. Note: if fields around the hidden rows are selected this way, right click + Show Rows doesn't work anymore, this might be a bug. Alternative 1: Text in status bar should reflect the number of currently selected and visible (eg. copied) rows and columns, and should update if rows/columns are hidden or shown. Alternative 2: Text in status bar should reflect the number of currently selected and visible (eg. copied) rows and columns, and the selection should not change if rows/column are hidden or shown. Alternative 3: Text in status bar should reflect the number of currently selected rows and columns, but the hidden rows/columns should be added in parentheses. For example: "51 rows (31 hidden), 1 columns selected" There could be further ideas, these are the ones that came to my mind. Based on the above, setting status to NEW, and since it has minimal effect on professional work, adjusting importance to low/trivial.
My vote for alternative #1. That would be in line with at least one similar spreadsheet application too. For me, counting or copying hidden cells doesn't make much sense. I rather see the ability to filter "away" cells a main purpose. To include these in any result contradicts that purpose.
There's actually been an earlier bug report on this. *** This bug has been marked as a duplicate of bug 84517 ***