LibreOffice Calc: Error 510, what's wrong with this formula?

Hi guys,
Currently trying to categorise some data based upon whether or not it is greater than or equal to the value in column F.

This is the formula I’m using

=COUNTIFS($A$2:$A$407,>F1,$A$2:$A$407,<=F2)

So basically I’m aiming to count all occurrences of values greater than the figure in F1 and less than or equal to the figure in F2.

However it’s not working and I get error 510 every time.

It does however work if I replace <F1 and <=F2 with "<0" and "<=365" which are the values in F1 and F2. As you can probably see, I’m working with length of time here and the values in column F are the number of days in 1 year, 2 years e.t.c. It’s a problem that’s pretty easy to workaround, just by placing the number in the formula as opposed to linking to the cell, but why it’s not working is a little beyond me.

I can imagine there’s going to be something remarkably obvious I’m missing…

All help appreciated :slight_smile:
Thanks

(Using LibreOffice 4.2.4.2 on Ubuntu 14.04)

Your text after “It does …” is unreadable for me. Try to write a grave accent before and after a formula to hide special characters like < to be interpreted as html tag, or write the formula into a separate line using the 1010-button of the editor, for example.

@Regina, I have fixed the formatting.

A criterion has the data type “string”. Only expressions, which result in a single number value are converted automatically.
Therefore you need to write =COUNTIFS($A$2:$A$407;">"&F1;$A$2:$A$407;"<="&F2) (my parameter delimiter is ;). The number values in F1 and F2 are automatically converted to string when they a concatenated to a string using &. The application help has some examples with correct syntax in the help for COUNTIF.

1 Like

A criterion has the data type “string”. Only expressions, which result in a single number value are converted automatically.
Therefore you need to write =COUNTIFS($A$2:$A$407;">"&F1;$A$2:$A$407;"<="&F2) (my parameter delimiter is ;). The number values in F1 and F2 are automatically converted to string when they a concatenated to a string using &. The application help has some examples with correct syntax in the help for COUNTIF.