Counting populating rows in Excel, but excluding first row-CodePudding

I am trying to count the number of populated rows in Excel, but excluding the first row as this is used as a header. ie. I want to start counting from the second row.

The following works to count populated rows, including the first row:

=COUNTIF(Books!A:A, "<>")

Logically, this is what I want, but it doesn't work in Excel:

=COUNTIF(Books!A2:A, "<>")

Seems like this should be simple? Am I missing something obvious? Thanks in advance!

CodePudding user response：

There are two possibilies:

1)Use the limit of Excel by entering the last possible row:

=COUNTIF(Books!A2:A1048576;"<>")

2)If the Header is always there, you could just substract it from the result:

=COUNTIF(Books!A:A, "<>")-1

CodePudding user response：

You need to count until last row, so if your data finish in row 20, then:

=COUNTIF(Books!A2:A20, "<>")

But if your data change rows quantity very often sometimes 20, sometimes 40, sometimes 10, I suggest you use a Table (a Listobject) because it works as a dynamic range:

My formula is:

=COUNTIF(Table1[Book Name],"<>")

Where Table1[Book Name] equals to range A2:A9 in this case. But if you add more data, Table1[Book Name] will refer to new range, and the formula will autoupdate :)

Using full qualified ranges as A:A may work and it's easy to use but it's a waste of resources checking a million cells every time you calculate. Besides, if you use a lot of formulas using full columns, it may overcharge the file and the time recalculations needs.