# CountiF cell has more than one value Or SUM(LEN(H12:H718)-LEN(SUBSTITUTE(H12:H718,";",""))

1. ## CountiF cell has more than one value Or SUM(LEN(H12:H718)-LEN(SUBSTITUTE(H12:H718,";",""))

I have a cell that has several entries, each entry is separated by ;
and i would like to count how many entries i have in the cell
example:
Subject one - research; subject two - marketing; subject three - Trade

as you see i have three subjects

if i use the function
=SUM(LEN(K12:K718)-LEN(SUBSTITUTE(K12:K718,";","")))

it counts the ;
but as you notice in the example I have three subjects, but only two ;

hope there could be a way to get what i need to count.

2. ## Re: CountiF cell has more than one value Or SUM(LEN(H12:H718)-LEN(SUBSTITUTE(H12:H718,";",

Yes you can add 1 as long as cells aren't blank, try

=SUMPRODUCT(LEN(K12:K718)-LEN(SUBSTITUTE(K12:K718,";",""))+1,(K12:K718<>"")+0)

3. ## Re: CountiF cell has more than one value Or SUM(LEN(H12:H718)-LEN(SUBSTITUTE(H12:H718,";",

Hi

Why don't you just put an +1 in the end?

=SUM(LEN(K12:K718)-LEN(SUBSTITUTE(K12:K718,";","")))+1

4. ## Re: CountiF cell has more than one value Or SUM(LEN(H12:H718)-LEN(SUBSTITUTE(H12:H718,";",

Column L entries :
Cell L12 = (UNKNOWN) ; Information: Company Information Loss
Cell L13 = Account Number
Cell L14 = Information: Non-Public Company Loss
Cell L15 = Account Number ; Customer Complaints: Responses

and so on, the separation between counts is the ;

If i use
=SUMPRODUCT(LEN(L12:L718)-LEN(SUBSTITUTE(L12:L718,";",""))+1,(L12:L718<>"")+0)
count = 928

If i use
=SUM(LEN(L12:L718)-LEN(SUBSTITUTE(L12:L718,";","")))+1
count = 234

If i use
=COUNTIF(L12:L717, "<>")
count = 695

5. ## Re: CountiF cell has more than one value Or SUM(LEN(H12:H718)-LEN(SUBSTITUTE(H12:H718,";",

Only you can tell which of those results is correct - I suggest you try on a small sample of 10 cells or so, count the results manually and see what the formulas give you:

This version

=SUM(LEN(L12:L718)-LEN(SUBSTITUTE(L12:L718,";","")))+1

is only adding 1 to the existing formula result, you'd need to nest the +1 inside the SUM function like this

=SUM(LEN(L12:L718)-LEN(SUBSTITUTE(L12:L718,";",""))+1)

confirmed with CTRL+SHIFT+ENTER

That should give the same result as my suggested formula unless you have blank cells in the range, in which case it counts 1 for those too, mine doesn't do that

6. ## Re: CountiF cell has more than one value Or SUM(LEN(H12:H718)-LEN(SUBSTITUTE(H12:H718,";",

If you can't get that to work, another idea may be to Find and Replace Subject One - , Subject Two -, and Subject Three - with a blank space, then do text to columns. You wouldn't have to worry about the syntax, and each of your subjects would be in a different column and easily countable. If you are able to manipulate your spreadsheet, this may be worth looking into.

7. ## Re: CountiF cell has more than one value Or SUM(LEN(H12:H718)-LEN(SUBSTITUTE(H12:H718,";",

Got it

This Formula is the winner

=SUMPRODUCT(LEN(L6:L13)-LEN(SUBSTITUTE(L6:L13,";",""))+1,(L6:L13<>"")+0)

As it will give the accurate count even if the cell is Blank

Thank you all.

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1