# 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!  Register To Reply

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)  Register To Reply

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...  Register To Reply

4. ## Re: Caculating Semi Colon in Range

Oops. Mine should be

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

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.  Register To Reply

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   Register To Reply

7. ## Re: Caculating Semi Colon in Range

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

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