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:


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


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


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


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


  • 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)