Counting in a Dynamic List

[gumbles]

Hi there,


I have looked throught Chandoo's website and found several functions (OFFSET or INDEX) that could apply to this but I can't seem to get it to work.


I have a list in say B7:B9 and Im using COUNTA to count if anything is put in those cells. The problem is I want the COUNTA function to work after I have inserted a row in the list.


Any help would be great.


Gumbles

 

[gumbles]

Ah solved it!


If you make the list a table it automatically updates the range for you.

 

[kaushik03]

Hi Gumbles,


Another workaround for this would be to create a named range and pass the same in the COUNTA function:


Say your data is from A1 to A10.


Press CTRL+F3 from keyboard to bring "name manager dialogue box",Click on "New", in the name box give any name of your choice (Say "MyRange" in this case[without quote]), now in refer to box write the below formula:


=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A,0)-1,1) and hit OK.


Now, if you write =COUNTA(MyRange) and enter


You will get the count of the data ranges from A1 to A10.


If you now make any changes in the range, add or delete any data, the named range will automatically expand or shrink, and formula will be updated automatically.


Kaushik