Gérard Fargeot <[email protected]> writes: >> I have in the range of O23:O27 on one sheet formulas: >> 1. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=5;K5:K24<=7)) >> 2. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=8;K5:K24<=15)) >> 3. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=16;K5:K24<=25)) >> 4. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=26;K5:K24<=34)) >> 5. cell: =DARABTELI(K5:K24;">=35") >> >> The cell K5 has the formula: >> =HA(CELLA("contents";'1.'.T$28)<>0;CELLA("contents";'1.'.T$28);".") >> >> In the cells: O23, O24, O27 everything is well. >> >> But in cells O25 and O26 I get ### instead of some result and in the >> status line there is an error: 'Error: wrong data type.' when those cells >> are active (one by one of course). >> >> What could be the problem here?
> You will have more answer if you post formula in English instead of > Hungarian. :) Can I do that from LibreOffice? How can I translate formula names? Must I change the Language environment for this? > dot (.) is a special character. In the menu Tools > Options > LO Calc > > Calculate, turned off "Enable regular expressions in formulas". > > Your formula =DARABTELI(K5:K24;ÉS(K5:K24>=5;K5:K24<=7)) (COUNTIF in > english) seems to be false. > ÉS(K5:K24>=5;K5:K24<=7) if validate as "normal formula" only checked if 1st > row (K5) is between 5 and 7. > If validate as an "array formula", checked if *all* the cells of the range > are between 5 and 7 and return an array of 1 if True, 0 if false. Ah I see now! Thanks! > If you want to count how many cells of the range are >=5 & <=7 , use > SUMPRODUCT : > =SZORZATÖSSZEG(K5:K24>=5;K5:K24<=7) Exactly that is what I want to do. Thanks for the information about the formula SUMPRODUCT! > It is also a very bad idea to use dot in sheetname. Always use alphanumeric > characters. I edited the formula and replaced dot '.' with zero "0". -- Regards, Pal -- For unsubscribe instructions e-mail to: [email protected] 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
