# COUNTIF: dynamic range

1. ## 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?

2. 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 ...

3. 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. 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.``

5. Thanks all a lot. Problem solved.

6. ## Re: COUNTIF: dynamic range

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

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