Caculating Semi Colon in Range

Hi All,

Good Evening. I need help to calculate the semicolon count in a range. The array contains data which is separated by a semicolon.

Column A

A;C;D
A;C
A;D;F;G;R
A;E;F
A;F;G;H;Y;T
A;G;T;Y;U

I want to calculate how many cells has more than 2 semicolons in a range. For the above example, the answer would be 3. I have tried this "=SUMPRODUCT(LEN(A:A)-LEN(SUBSTITUTE(A:A,";","")))" formula. but I don't know how to include the countif formula in this. Thanks in advance!

I am not sure but:

Formula:

Please Login or Register  to view this content.

or as with array formula:

Please Login or Register  to view this content.

(accept with CSE)

Hi try this:

=SUMPRODUCT(--(LEN($A$2:$A$7)-LEN(SUBSTITUTE(A2:A7,";",""))=2))

Don't use whole column ranges with SP. It can get slow...

Oops. Mine should be

=SUMPRODUCT(--(LEN($A$2:$A$7)-LEN(SUBSTITUTE(A2:A7,";",""))>2))

Thanks for your reply, Kokosek. It works fine as I expected. It will be more useful for me to learn if it is with sumproduct formula.

EDIT: delete. 1st Glenn's formula counted incorrectly.
I don't see the point to use SUMPRODUCT if COUNT do the job but its your choice

Great Glenn. Thanks for your valuable time. You made my day.