Count the number of lines in a column where some cells have multiple lines

Question

How to count the number of lines when some cells in a column contain multiple lines? Example:

+----------------------+
| text                 |
+----------------------+
| multiline            |
| text                 |
| here                 |
+----------------------+
|                      |
+----------------------+
| some cells are empty |
+----------------------+

The function counta helps by counting nonempty cells, but it does not take into account multiple lines in the same cell.

Answer 1

This can be done by the following combination:

=counta(A2:A20) + sum(arrayformula(len(regexreplace(A2:A20, "[^\n]", ""))))

The first part, counta, counts each nonempty cell once. Then, regexreplace removes everything that is not a newline character \n. The len function then counts how many linebreaks the cell contains, and they are added up.

For the example above, there are 3 nonempty cells, and one of them contains 2 line breaks. So the formula returns 3 + 2 = 5.