Count cells that contain anything OTHER THAN - Help Please

mollys

New Member
Joined
Oct 14, 2009
Messages
14
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:

Facebook, Instagram
Instagram
Facebook, A friend told me about you
I walked by an event
Instagram, My mom

<tbody>
</tbody>

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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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))
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
How about:

ABC
1Facebook, Instagram3
2Instagram
3Facebook, A friend told me about you
4I walked by an event
5Instagram, My mom

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
C1=SUMPRODUCT(--(LEN(SUBSTITUTE(SUBSTITUTE(A1:A6,"Instagram",""),"Facebook",""))>3))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top