[thelist] An excel question (well, Open Office actually)

[john at johnallsopp.co.uk]

>> I've got a field for the 1900 and one for the 1999, so
>> ideally I want to be able to put in <=i1 and >=j1 but it
>> gives me 510 error which translates like it's missing a
>> variable, as if I should have something before the <, but
>> that can't be a field because we're applying it to all the range.
>
> IIRC, COUNTIF's "criteria" parameter expects a value, or a cell that
> evaluates
> to one, not a criteria string.
>
> You could create a column that rounds each item down to the first year
> of it's
> applicable range, and then countif() those.

A friend helped me out.

I made two range columns, so that G contained the numbers, H contained
the 'from' in the range, and I contained the 'to'. Then in J I used
COUNT(G1:G565)-COUNTIF(G1:G565;"<"&H2)-COUNTIF(G1:G565;">="&I2)

Worked a treat :-)

Cheers
J