Count cells that contain anything OTHER THAN - Help Please

[mollys]

Hello all -
Hoping you can help me out here. I am trying to count up cells that contain freeform text and can't seem to figure it out.

The column is a 'How did you hear about us' list and it includes three standard options plus a freeform other field. Users can select multiple options so I end up with a column like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Facebook, Instagram[/TD]
[/TR]
[TR]
[TD]Instagram[/TD]
[/TR]
[TR]
[TD]Facebook, A friend told me about you[/TD]
[/TR]
[TR]
[TD]I walked by an event[/TD]
[/TR]
[TR]
[TD]Instagram, My mom[/TD]
[/TR]
</tbody>[/TABLE]

I am looking to count up the cells that contain ANYTHING other than "Facebook" or "Instagram". In this example, the count would be 3.

Any help would be greatly appreciated. Thanks so so much.

 

[Tetra201]

Could you please clarify the logic behind the expected result of 3?

 

[JrayK2]

There might be a better way to do it but this should work (replace A1 relevant cell reference)


=(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1)-(IF(ISNUMBER(SEARCH("Instagram",A1))=TRUE,1,0)+IF(ISNUMBER(SEARCH("Facebook",A1))=TRUE,1,0))

 

[mollys]

Could you please clarify the logic behind the expected result of 3?

Yes, sorry about that.

Three of the cells include text other than the standard answers of "Facebook" and "Instagram" -
"I walked by an event", "My mom", and "A friend told me about you" are the freeform answers that I would like to count. So I'm trying to count cells that contain any text BESIDES the standard answers. Does that make sense?

 

[JrayK2]

Yes, sorry about that.

Three of the cells include text other than the standard answers of "Facebook" and "Instagram" -
"I walked by an event", "My mom", and "A friend told me about you" are the freeform answers that I would like to count. So I'm trying to count cells that contain any text BESIDES the standard answers. Does that make sense?

What is the 3rd standard option? Besides instagram and facebook

 

[kweaver]

While this is ugly, it works (I think).
If your responses are in the A column (starting in row 2; header in row 1; I tested with 6 responses; adjust the formula accordingly), then

B1: =COUNTA(A:A)-1-COUNTBLANK(B2:B7)
B2: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Facebook",""),"Instagram",""),",","")," ","")

This substitution removes the key words, the ,s and all spaces. Therefore, your first two entries would be blank but the others would have text.
Fill B2 down to however many rows you have.

If there might be other characters other than a comma and space, you could (a) add more substitution instructions or (b) would have to go to VBA.

Here's a UDF (User-Defined-Function) in VBA that could be placed in, say, F2 and fill down. Put the same B1 formula in F1
I put a name range, remove, in D1:Dn which could include a space, comma, the key words, etc., etc.
Then, in F2 write: =REMOVE_STUFF(A2,remove) and fill this down.
Now the UDF will remove all of the undesired characters like a group of SUBSTITUTES would.

HTH

Function REMOVE_STUFF(strInput As String, rngFind As Range) As String
    
Dim strTemp As String
Dim strFind As String

strTemp = strInput

For Each cell In rngFind
    strFind = cell.Value
    strTemp = Replace(strTemp, strFind, "")
Next cell
    
REMOVE_STUFF = strTemp
    
End Function

 

[mollys]

The third option is Google. It didn't show up in the cells that I copied in my example, but does show up in the field.

 

[Eric W]

How about:

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Facebook, Instagram[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Instagram[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Facebook, A friend told me about you[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]I walked by an event[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Instagram, My mom[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=SUMPRODUCT(--(LEN(SUBSTITUTE(SUBSTITUTE(A1:A6,"Instagram",""),"Facebook",""))>3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]