Probably a simple answer, but I can't seem to figure it out

[JONNYCANADA]

JT00295576 JT00350215
H048579632 MT00394791 M040529851
J041757899
HT005262686 J017612581 MT00220848
JT00371377
MT00337725
H042053736

<tbody>
</tbody>

So I am trying to find a formula that would count how many accounts are listed; there are 12 accts in the example. most formulas i try give me a count of the cells with something in them (7). The accounts I use start with different letters for different locations (MT,M, JT, J, HT, H). i also use ALT ENTER to put multiple accounts in a cell.

any suggestions for how to get a count of these accounts?

 

[AliGW]

Do I take it that there are sometimes two or three accounts listed in one cell? If so, why?

 

[oldbrewer]

a	1	1	#####
b	1	1
c	1	1
d e f	5	3
g	1	1
h	1	1
I j k l m n o	13	7
p	1	1							a4	3
q	1	1							a7	7
r	1	1							a11	2
s t	3	2							a13	4
u	1	1
v w x y	7	4
z	1	1
			26
		#####
		=IF(B1=1,B1,(B1+1)/2)
		B1=len(A1)

<tbody>
</tbody>

 

[JONNYCANADA]

Do I take it that there are sometimes two or three accounts listed in one cell? If so, why?

they are one person's accounts as they have gotten services from different locations

 

[JONNYCANADA]

#####
=IF(B1=1,B1,(B1+1)/2)
B1=len(A1)

do i use 2 different cells for those formulas?
how do i set them up?

 

[JONNYCANADA]

a	1	1	#####
b	1	1
c	1	1
d e f	5	3
g	1	1
h	1	1
I j k l m n o	13	7
p	1	1							a4	3
q	1	1							a7	7
r	1	1							a11	2
s t	3	2							a13	4
u	1	1
v w x y	7	4
z	1	1
			26
		#####
		=IF(B1=1,B1,(B1+1)/2)
		B1=len(A1)

<tbody>
</tbody>

i can't seem to get this work...
does this count how many characters are in a cell? - because my accounts are always 10 characters long with letters and numbers.
could u show me how to use this formula with my example or at least a few of those accounts?

 

[AliGW]

OldBrewer has gone offline. I'll have a look and see if I can help.

 

[Sunny Kow]

Hi

Assuming your data is in column A, you can enter this into column B and copy down as many rows as needed. Then you can sum column B.

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

 

[AliGW]

Hi

Assuming your data is in column A, you can enter this into column B and copy down as many rows as needed. Then you can sum column B.

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

That's just what I was going to suggest - a helper column!

 

[Sunny Kow]