Google Sheets – Count Lines in Column with Multiple Lines

google sheets

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.

Best Answer

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.

Related Topic