0

I have a simple Excel sheet, containing different columns, each of them having their title. Each of the cells, containing the title, have a name, e.g. the column containing the first entries (key values), starts with the cell with the name Header_Title.

In order to know the amount of entries, which is filled in, I had a very simple idea: lets count the number until the end of the table, so I ended up with something like:

=COUNTA(OFFSET(Header_Title;1;0):...) // I've forgotten how I did it.

However, At one moment I've removed some tuples, and now my formula looks like:

=COUNTA(OFFSET(Header_Title;1;0):#REF!)

How can I say:
Continue to the end of the column but if any tuples get deleted, don't change this formula?

Does anybody have an idea?
Thanks

Dominique
  • 16,450
  • 15
  • 56
  • 112

1 Answers1

1

Why not using a "normal" Table? and a formula like:

=AGGREGATE(2;4;Table1[Header_Title])

or:

=SUBTOTAL(103;Table1[Header_Title])
EvR
  • 3,418
  • 2
  • 13
  • 23
  • The issue is, how to find the end of the table? `Header_Title` is just one cell, containing the title of the column. I need to know how to define the table, e.g. `Table[Header_Title:...]` (what to put in the three dots?). – Dominique Jul 31 '18 at 11:39
  • 1
    This is a possibility: =COUNTA(INDEX(1:1048576;;COLUMN(Header_Title)))-ROW(Header_Title) When below your "table" all cells are blank – EvR Jul 31 '18 at 12:11
  • But when using real Tables (insert Table eg Ctrl-T) there's no need for complicated formulas – EvR Jul 31 '18 at 12:12
  • But that is just one of the powers of Tables, they will grow dynamically – EvR Jul 31 '18 at 12:26