PDA

View Full Version : Formula for counting used rows - Anomalie?



Simon Lloyd
05-21-2007, 03:21 AM
Hi all, i have answered a couple of posts lately where the Op's have asked for a way to count used rows with a worksheet function - well i supplied the answer like this:


for counting rows, you need to create a dynamic Named Range for column A using, INSERT, NAMES, DEFINE - type a name for your range (i used ColA) and then copy and paste this in to the Refers To window =OFFSET(Sheet1!$A$1,0,0, COUNTA(Sheet1!$A:$A),1) now click ADD (a great worked example and explanation can be found here (http://www.contextures.com/xlNames01.html), once you have created your name range enter this in to any cell =ROWS(ColA) where ColA was my named range.

it works well and if you want to count from row 2 then =ROWS(ColA)-1 as =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1) still counts total number of rows. The anomalie i found was if i changed the OFFSET to =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A2:$A65536),1) then using =ROWS(ColA) showed a different result depending on where you used it..if you used it in row 1 then you get the total count of used rows from row 2 all well and good but try to copy or type =Rows(ColA) elsewhere!, you get an unexpected result for some reason every time you use the formula after the initial one the Dynamic Named Range Refers To box has a different formula in it?

Any ideas or suggestions?
EDIT: Yes i did forget to say that COUNTA(A:A) does the same job, completly slipped my mind at the time, sledgehammer to crack a nut eh?

Bob Phillips
05-21-2007, 04:05 AM
Surely, it is because the formula is adjusted to the row that it is in compared to the row it was defined in, because you aren't using absolute rows.

Try

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$65536),1)

and see if that is consistent.

Simon Lloyd
05-21-2007, 04:13 AM
Thanks Bob, but i understand about absolute references, what i don't understand is how the actual dynamic named range formula changed simply by using =Rows(ColA) in another cell below row 1, i suppose what i dont understand is that the formula for the named range is typed in the names|define window and the name i used can be called on anywhere to look at that range so why when i use A2:A6536 should that make a difference to how the range is percieved over A:A when i make reference to that range within other formula in any other cell than those in row 1?

Have i made sense is it my usual rubish?