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.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,542
Could you please clarify the logic behind the expected result of 3?
 

JrayK2

New Member
Joined
Jul 29, 2017
Messages
8
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

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

New Member
Joined
Jul 29, 2017
Messages
8
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

Well-known Member
Joined
May 8, 2018
Messages
1,076
Office Version
365, 2010
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:

mollys

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,922
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,102,907
Messages
5,489,659
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top