Count the sum of the number of lines in a column

[Ian Y]

In Google Sheets, if you want to count the sum of the number of lines in all cells in column A, this function can be used:

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

What’s the equivalent in Excel 2019? I tried the same function, and it doesn’t work and shows the result as #NAME?

 

[kevin9999]

I'm not sure exactly what you're after: sum (add) the values in column A; count the number of non-empty cells in column A? Something else?

 

[Ian Y]

@kevin9999,

For example, if the first cell of column A has these 4 lines:

This is a line.
Yet another line.
How are you?
What time is it?

And the second cell of column A has these 3 lines:

More lines go here.
Today is sunday.
This year is 2023.

lines:

And one of the other cells of column A has these 1 line:

Just one more line.

Then the function I need would yield 8

The number of cells and lines inside is unpredictable.

 

[kevin9999]

OK, it's the number of sentences in the column?

 

[Ian Y]

OK, it's the number of sentences in the column?

Each line can be a sentence, a url, a string of number, a word, etc. As long as it ends with a line break (when there are multiple lines in a cell), it needs to be counted as one line.

 

[Ian Y]

OK, it's the number of sentences in the column?

And yes, it's the number of lines that is needed.

I wish I could edit my post.

 

[kevin9999]

It looks to me like it's a case of counting all the line breaks (plus 1 for each cell) in the entire range of interest. Not really my area, but hopefully someone else on the forum will come up with a solution for you

 

[Dave Patton]

With earlier versions of Excel you will probably need to use SumProduct.

T202311a.xlsm
	A	B	C	D
1
2	ABC	1	5	5
3	ABC ABC	2
4	ABC ABC	2
5
4i

Cell Formulas
Range	Formula
C2	C2	=SUM((LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,CHAR(10),""))+1))
D2	D2	=SUMPRODUCT(--(LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,CHAR(10),""))+1))
B2:B4	B2	=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1

 

[Peter_SSs]

It looks to me like it's a case of counting all the line breaks (plus 1 for each cell) in the entire range of interest.

I think that is correct. Here is a formula to try. It may need confirmation with Ctrl+Shift+Enter in Excel 2019

23 11 19.xlsm
	A	B
1
2	This is a line. Yet another line. How are you? What time is it?	8
3	More lines go here. Today is sunday. This year is 2023.
4	Just one more line.
Sheet2 (3)

Cell Formulas
Range	Formula
B2	B2	=SUM(LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,CHAR(10),""))+1)

 

[Ian Y]

@Dave Patton, @Peter_SSs

Thanks. The Sum function really has problem in this case in Excel 2019. It only yields 4 instead of 8.

As for SumProduct, it looks to be the solution for this case. I think it just needs some tweaks. I tried:

=SUMPRODUCT(--(LEN(A1:A1000)-LEN(SUBSTITUTE(A1:A1000,CHAR(10),""))+1))

It yields 1005 instead of 8, apparently counting blank cells as well. For this reason, I did some searches on the internet and found a tutorial on excluding blank cells when using SumProduct. By following its guide on using NOT(ISBLANK()), I came up with this function:

=SUMPRODUCT(--(LEN(NOT(ISBLANK(A1:A1000)))-LEN(SUBSTITUTE(NOT(ISBLANK(A1:A1000)),CHAR(10),""))+1))

However, it still incorrectly yields 1000 instead of 8. What did I do wrong?