# Caculating Semi Colon in Range

1. ## 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!

2. ## Re: Caculating Semi Colon in Range

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)

3. ## Re: Caculating Semi Colon in Range

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...

4. ## Re: Caculating Semi Colon in Range

Oops. Mine should be

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

5. ## Re: Caculating Semi Colon in Range

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.

6. ## Re: Caculating Semi Colon in Range

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

7. ## Re: Caculating Semi Colon in Range

There are currently 1 users browsing this thread. (0 members and 1 guests)