Simple Formula Issue

[ChrisFoster]

Hi all,

I feel a bit silly here. I have the following simple formula that works, it brings back a value of 25 which is correct.

=COUNTA('Exception Breaches'!A2:A100)

However, as the data in column A may exceed 100, I am trying the below code but it just brings back a value of 1. What am I doing wrong?

=COUNTA('Exception Breaches'!A2:A)

 

[MountainFog]

You would need to change it to A:A. Excel will not accept A2:A
Assuming that there is something in cell A1:

=COUNTA('Exception Breaches'!A:A)-1

 

[arthurbr]

Why not make your range an Excel Table ? The formula will then adapt automatically to the length of the range

 

[ChrisFoster]

Why not make your range an Excel Table ? The formula will then adapt automatically to the length of the range

I dont want to. I just want the formula to work.

 

[Fluff]

Another option is just increase the range to something you will never exceed like

=COUNTA('Exception Breaches'!A2:A10000)