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?

 

[Phuoc]

One way:

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

 

[Peter_SSs]

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

It may need confirmation with Ctrl+Shift+Enter in Excel 2019

Did you confirm the formula with Ctrl+Shift+Enter, not just Enter?

 

[Ian Y]

One way:

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

That works! Thanks for providing a solution!

 

[Ian Y]

Did you confirm the formula with Ctrl+Shift+Enter, not just Enter?

Oh, I didn't do that.

I just tried Ctrl+Shift+Enter, and the only difference I saw is that function text in the function field are now wrapped in curly brackets ({}), and the number they yields are the same (1005 and 1000).

 

[Peter_SSs]

and the number they yields are the same (1005 and 1000).

OK, so blank cells in the range then? Not sure what highest row number you will need , but try something like this.
Will you have a lot of these calculations to do in this worksheet?

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

Cell Formulas
Range	Formula
B2	B2	=SUM(LEN(A2:A1000)-LEN(SUBSTITUTE(A2:A1000,CHAR(10),""))+(A2:A1000<>""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Ian Y]

@Peter_SSs,

That works, too! Thanks for showing that Sum can also work!

 

[Ian Y]

@Peter_SSs,

As for the amount of calculations, I probably will have 90 columns in total. Each column represent one day. The number of rows would be about 400 ~ 500 rows in total.

 

[Peter_SSs]

That works, too! Thanks for showing that Sum can also work!

You're welcome. Thanks for the confirmation.

I probably will have 90 columns in total. Each column represent one day. The number of rows would be about 400 ~ 500 rows in total.

OK, thanks. That is not all that large. I thought if the amount of rows was very big, and there were a lot of them, the SUMPRODUCT formulas might be somewhat slow to calculate. With your amount of data I don't think any difference either way would be noticeable.