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 05:23 PM.
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 07:34 PM.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
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)
If you don't mind using a helper cell, then:
Where Z1:=COUNTIF(INDIRECT(Z1),"<>0")
="A2:A" & COUNTA(A:A)
---
Ben Van Johnson
Thanks all a lot. Problem solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks