Goo Day
I am trying to count number of rows in my column of spreadsheet using a combination of OFFSET and COUNTA.
I am getting a null value all the time.
It seems I am missing a something in the formula. I have created a name range as well, that does not work either.
thanks
There is no indication in the attachment of what you are trying to count or where the answer should be. Please be more specific and tell us what your expected outcomes are.
PS - If your user name is a real E-mail address, then I would ask an administrator to change it for you - it's likely to be harvested by spam bots.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Reference to the Attachment - SHeet4, the cell above the username I am trying to insert the formula. My current formula I have is:
=OFFSET($A$1;0;0;COUNTA($A$4:$A$15;1))
The above does not seem to work
What is your expected answer and why? The current formula makes no sense, so I cannot work out what you are trying to do with it.
Try this:
=COUNTA($A$4:$A$18)
Last edited by AliGW; 04-14-2017 at 03:15 AM.
My expected answer is to count the number of rows for that column. I can use the COUNT function, however the range will expand with time with more information being loaded. Hence I need the range to be dynamci
Hence I am trying to use the OFFSET and COUNTA function.
It will be because you have used a table. Use this:
=COUNTA($A$4:$A$11)
then add a couple of extra rows and check what happens.
PS As I said, you really need to get that user name changed if it really is an E-mail address.
HI Ali
Your suggested formula works, Thanks. However when I am adding new information, the Formula does not count the new Data.
Yes, it does. Because you have used a table, each time you add a row (a value in column A), the formula expands to include it. Have you tried adding extra rows?
Last edited by AliGW; 04-14-2017 at 03:33 AM.
Look:
Excel 2016 (Windows) 32 bit
Afiya Kassim Ahmed Murchie Ahmed Sha Akeel Dhudraj Amina Cassimjee Aneesa Abrahams Anisa Suliman Attiyah Motani Attiyah Motani bggg hahahah bebebeb shshshs
Sheet: Sheet4
The formula has expanded to include the rows I've added:
Excel 2016 (Windows) 32 bit
Sheet: Sheet4
Hi Ali
Works perfect. Thank You.
Great - you're welcome!
Get that user name changed!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Hi,
if you lazy to adjust, you can try
=COUNTA(A:A)-2
2 represent the number of rows you do not want include in the count.
but, it may cause slow for the workbook. it upto your choice.
it you want to revert a to selected row text. "index" function is more suitable for you
Last edited by BoredWorker; 04-14-2017 at 04:06 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks