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)