# COUNTIF and the data range

LibreOffice 4.3.7 , Windows 7 The data has 1500 rows, and I used the data range $A$1:$A$1048576 for the formula =IF(COUNTIF($A$1:$A$1048576,A1)>1,"Y","") even it's only 1500 rows , then I tried to add one row between row 1 and row 2 and got the error message "$REF!". edit retag close merge delete ## 1 Answer Sort by » oldest newest most voted If you insert a Row, the Range in formula expands to$A$1:$A$1048577 which is out of available Rows, →→ $REF-error

more

So it can't be converted into $A:$A automatically like MS excel when the maximum number of rows have been reached.

( 2015-07-01 10:10:47 +0200 )edit

@lolax: exactly

( 2015-07-01 10:35:36 +0200 )edit