+ Reply to Thread
Results 1 to 6 of 6

COUNTIF: dynamic range

  1. #1
    Registered User
    Join Date
    12-20-2008
    Location
    Lisbon
    Posts
    14

    COUNTIF: dynamic range

    I want to perform a COUNTIF in a column where data is added regularly. The COUNTIF range is to be the last 100 rows of data: so if yesterday that range was a2:a102 then today it should be a3:a103, tomorrow it will be a4:a104 and so on.

    How can I get COUNTIF to change the range as new data is added? I can generate a string with the changing range ("a2:a102", "a3:a103", and so on) but how do I put that into COUNTIF?
    Last edited by VBA Noob; 01-05-2009 at 06:23 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,656
    Welcome to the forum.

    Maybe =COUNTIF(INDEX(A2:A65536, COUNTA(A2:A65536)):INDEX(A2:A65536, COUNTA(A2:A65536) - 99), yourCountCriterion)

    No blank cells among the data ...
    Last edited by shg; 12-20-2008 at 08:34 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    A2:A102 is 101 rows so assuming you want the last 101.....

    Assuming you don't have any blanks, i.e. A1 has a header and then all subsequent rows are filled until data ends then you can use this formula to generate the range

    =OFFSET(A2,COUNTA(A:A)-102,,101,1)

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,279
    If you don't mind using a helper cell, then:
    Please Login or Register  to view this content.
    Where Z1:
    Please Login or Register  to view this content.
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    12-20-2008
    Location
    Lisbon
    Posts
    14
    Thanks all a lot. Problem solved.

  6. #6
    Registered User
    Join Date
    01-11-2012
    Location
    Italy
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: COUNTIF: dynamic range

    Thanks a lot.... even after years this post is good for someone... me
    Bye

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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