Counting the number of bullet points in a range. Easier method?

[claybwagner28]

Hello, I have a spreadsheet that is sent out monthly. The users will often give answers using ALT+ENTER within a cell if their are multiple departments that apply they use bullet points for easier reading.
I need to prepare a board report that counts the entire number of departments that are listed in Column B. COUNTA returns 3 but the answer I really am looking for is 6.

I can use =LEN(B1)-LEN(SUBSTITUTE(B1,”Bullet Point”,””) which gives me 2. (Can’t type the bullet point because I am on my iPad but hopefully that made sense.)

I could ultimately create =(LEN(B1)-LEN(SUBSTITUTE(B1,”Bullet Point”,””))+(LEN(B2)-LEN(SUBSTITUTE(B2,”Bullet Point”,””))+(LEN(B3)-LEN(SUBSTITUTE(B3,”Bullet Point”,””))
that gives me 6 but is there an easier method?

As always thanks in advance!
Clay

A	B
Exams Not Scheduled	Finance Risk
Current Exams	Mortgage Treasury Management Wires
Closed Exams	Branch Operations

 

[Tetra201]

Try

=SUM(LEN(B1:B3)-LEN(SUBSTITUTE(B1:B3,"•","")))

 

[claybwagner28]

Thanks!!