Gérard Fargeot <

[hidden email]> 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".