How to get not empty row count?

Question

I find myself doing a lot of operations on tables where I am not sure about entry count.

I simply guess that it should be lower than 100 and just do =SUM(A1:A100). Now if I have only 2 entries, all the other rows are useless for other things.

How can I solve this problem? Maybe I can automatically detect continuous values without an empty row in between or something?

I am not about performance. If I use 100 rows for some formula just to be safe in the future but only 3 rows have values present I just wasted a lot of spreadsheet space making it harder to use and read.

EDIT

To explain what I mean by saying 'waste of space'.

I don't know how many name:value pairs I will have. Maybe 5 maybe 100. So in this case I have 3 entered but 5 empty columns. That means I have wasted 2 columns of space. When I want to be sure my calculations will handle a lot of values, I just do like =SUM(A2:A100) and leave it like that but then it's impossible to place another attributes or more values.

Answer 1

I'm still not convinced how one could 'waste spreadsheet space' and I would recommend using simply =SUM(A:A) in such a case.

If you must sum up to the very last cell in column A, then maybe this formula would suit you:

=SUM(A1:INDEX(A:A,MATCH(9^99,A:A)))

This formula will ignore any blanks if any and count down to the last value.

Another possible (and maybe simpler) formula is with SUMIF:

=SUMIF(A:A, "<>0")

Since blanks are considered as 0, they won't get summed, but as I said, I find it much simpler to just use SUM(A:A) since blanks are zeros anyway.

Answer 2

You can use =CAUNTA() function.

COUNTA