Created attachment 72285 [details] contains the formula and some data starting from this link: Source: Excel Count Functions -- Count Excel Cells Address : <http://www.contextures.com/xlFunctions04.html#Visible> =SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)-MIN(ROW(A1:A10)),,1)), -- (A1:A10=A12)) I applied it on a document: once opened in libreoffice the formula gives an error while in excel it does work fine. =MATR.SOMMA.PRODOTTO(SUBTOTALE(3;SCARTO($G$9:$G$260;RIF.RIGA($G$9:$G$260)-MIN(RIF.RIGA($G$9:$G$260));;1)); --($G$9:$G$260=H4)) I attach the small sample file
the formula has been tested both in libreoffice and apache openoffice, no way I just wonder whether there is a more simple function to get what I need, to count precise values in a filtered list
reproducible with LO 4.0.1.2 (Win7 Home, 64bit), marked as bug @Markus: Could this issue maybe be something for you?
With Excel SUBTOTAL(9;OFFSET(A1;{1;3;4};0)) returns an array of SUBTOTAL results : {SUBTOTAL(9;A2);SUBTOTAL(9;A4);SUBTOTAL(9;A5)} With LibO/AOO the same formula returns a scalar which is the result of : SUBTOTAL(9;{A2;A4;A5}) So SUBTOTAL in Excel treats the result of OFFSET as 3 ranges of 1 cell while in LibO this is a range of 3 cells. I don't know where is the truth :)
A workaround for your sample : Add a column with the formula =SUBTOTAL(3;A9) in H9 and drag down (with filter to "All") Then =SUMPRODUCT($H$9:$H$260; --($G$9:$G$260=H4)) will gives you the result with a simpliest and shorter formula. You need 3.6.7/4.0.4/4.1.0 minimum, see https://bugs.freedesktop.org/show_bug.cgi?id=64384
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.