Search code examples
excelexcel-formuladynamicrange

Dynamic Range used Excel

I have a series of number of my column A,, from A1 to A100 going from 1 to 9

Now I want to count the number of times the number 1 appears, it is easy:

=COUNTIF(A1:A100;1)

Second step I want to count only on the 12 last values, still easy:

=COUNTIF(A89:A100;1)

Third step, I want to dynamise that, for when I add a new value on my cell A101, the formula change automatically like that:

=COUNTIF(A90:A101;1)

I already managed to get the number of filled cells like that:

=COUNTIF(A:A;"<>"&"")

So theorically I sould do something like:

=COUNTIF("A"& (COUNTIF(A:A;"<>"&"")-11) & ":A" & COUNTIF(A:A;"<>"&"");1)

But it doesn't work :(

Can someone helps me? Thanks

Solution

  • COUNTA directly counts non-empty cells.

    The generated result is a text, therefore must use INDIRECT to transform it to a range.

    =COUNTIF(INDIRECT("A"& (COUNTA(A:A)-11) & ":A" & COUNTA(A:A));1)