+ Reply to Thread
Results 1 to 13 of 13

Offset and counta functions

  1. #1
    Registered User
    Join Date
    11-04-2010
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    38

    Offset and counta functions

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,458

    Re: Offset and counta functions

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    11-04-2010
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    38

    Re: Offset and counta functions

    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

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,458

    Re: Offset and counta functions

    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.

  5. #5
    Registered User
    Join Date
    11-04-2010
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    38

    Re: Offset and counta functions

    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.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,458

    Re: Offset and counta functions

    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.

  7. #7
    Registered User
    Join Date
    11-04-2010
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    38

    Re: Offset and counta functions

    HI Ali

    Your suggested formula works, Thanks. However when I am adding new information, the Formula does not count the new Data.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,458

    Re: Offset and counta functions

    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.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,458

    Re: Offset and counta functions

    Look:

    Excel 2016 (Windows) 32 bit
    A
    2
    13
    3
    User Name
    4
    Afiya Kassim
    5
    Ahmed Murchie
    6
    Ahmed Sha
    7
    Akeel Dhudraj
    8
    Amina Cassimjee
    9
    Aneesa Abrahams
    10
    Anisa Suliman
    11
    Attiyah Motani
    12
    Attiyah Motani
    13
    bggg
    14
    hahahah
    15
    bebebeb
    16
    shshshs
    Sheet: Sheet4

    The formula has expanded to include the rows I've added:

    Excel 2016 (Windows) 32 bit
    A
    2
    =COUNTA($A$4:$A$16)
    Sheet: Sheet4

  10. #10
    Registered User
    Join Date
    11-04-2010
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    38

    Re: Offset and counta functions

    Hi Ali

    Works perfect. Thank You.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,458

    Re: Offset and counta functions

    Great - you're welcome!

    Get that user name changed!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,458

    Re: Offset and counta functions

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  13. #13
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Offset and counta functions

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. using offset and counta to get the last text
    By ash3angel in forum Excel General
    Replies: 2
    Last Post: 04-22-2015, 07:10 AM
  2. [SOLVED] named ranged - offset & counta
    By milo1984 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-26-2014, 02:13 AM
  3. [SOLVED] +OFFSET and COUNTA in VBA
    By Baphomay in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2013, 08:11 PM
  4. Data validation, IF, OFFSET & COUNTA
    By alfgrey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2013, 08:27 PM
  5. [SOLVED] Using OFFSET and/or COUNTA within SUMPRODUCT Function
    By maacmaac in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-21-2012, 03:51 AM
  6. OFFSET/COUNTA Function and Charts
    By Dgreiner in forum Excel General
    Replies: 3
    Last Post: 03-11-2012, 02:26 PM
  7. OFFSET/COUNTA merged cells
    By dborchardt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2006, 08:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1