Results 1 to 6 of 6

Thread: counting dynamic end of multiple arrays

  1. #1

    Cool counting dynamic end of multiple arrays



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi. can anyone help me with this; every hour new numbers are added in six arreys. there are now 1348 rows and new are coming every hour....how do i count the last 10 entries? i am filling in the new numbers in a form....

  2. #2
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    Try this, =SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)) assuming values are in sheet1 in column A
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  3. #3
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0

    Note to self:

    Doh!, should read the entire question properly!

    Use this: =SUM(OFFSET(A1,MATCH(1E+30,A:A)-1,1,-10,1))

    EDIT: for use in another sheet: =SUM(OFFSET(Sheet1!A1,MATCH(1E+30,Sheet1!A:A)-1,1,-10,1))
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  4. #4
    Without OFFSET

    =SUM(INDEX(A:A,MATCH(99^99,A:A)-9):INDEX(A:A,MATCH(99^99,A:A)))

  5. #5
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    Without OFFSET

    =SUM(INDEX(A:A,MATCH(99^99,A:A)-9):INDEX(A:A,MATCH(99^99,A:A)))
    Smarter way Bob!
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  6. #6
    Problem solved :-) you are the man bob...........thanks for all replies

Posting Permissions

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