count cells that string contains X but does not contains Y or Z

[gaz_11]

Hi, sooo.....

I have list in table 1 (below) and I want to count in table 2, the number times the text in table 2 column A appear in the list from table 1. BUT i need to to exclude any entries that are in A4 or A5 from table2.

I can do count if but i cant figure out how to exclude.

for the count if ive got in table2 B1. =COUNTIF('table1'!$A:$A,"*"&A1&"*")

	A
1	ab/home/west
2	ab/home/east
3	ab/home/east/up
4	ab/home/up
5	ab/home/down
6	ab/away/east
7	ab/away new/west
8	ab/state/east
9	ab/state new/east
10

<tbody>
</tbody>

	A	B
1	up
2	down
3	east
4	away
5	state
6
7
8
9

<tbody>
</tbody>

 

[alansidman]

Deleted response. Misread OP's request.

 

[MARZIOTULLIO]

	A​	B​	C​	D​
1​	ab/home/east		up	2​
2​	ab/home/east/up		down	0​
3​	ab/home/up		east	4​
4​	ab/home/down		away	1​
5​	ab/away/east		state	2​
6​	ab/away new/west
7​	ab/state/east
8​	ab/state new/east
9​

<tbody>
</tbody>

D1=COUNTIFS($A$1:$A$8,"*"&C1&"*",$A$1:$A$8,"<>"&$A$4,$A$1:$A$8,"<>"&$A$5) copy down

 

[alansidman]

Which sheet are you referring to when you wish to exclude A4 and A5. What is the correct answer for each item? Confused by your request.

 

[gaz_11]

its form the 2nd table in my original post (it didn't include grid line when i posted so not clear) so in this case its 'away' and 'state' that i want to exclude. But it need to have a wild card to cover 'away new'and 'state new'

 

[alansidman]

Your explanation is still not clear. Suggest you show us your expected results so that we can better understand.

 

[gaz_11]

A​	B​	C​	D​
1​	ab/home/east		up	2​
2​	ab/home/east/up		down	1​
3​	ab/home/up		east	2​
4​	ab/home/down		away	2​
5​	ab/away/east		state	2​
6​	ab/away new/west
7​	ab/state/east
8​	ab/state new/east
9​

<tbody>
</tbody>

So i want to count the number of times up, down and east appear in B but only if these dont have away or east in the string.

 

[alansidman]

Best I can do is to completely avoid Away and State with this code.

Option Explicit


Sub Directions()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim lr1 As Long, lr2 As Long
    Dim i As Long, j As Long, x As Integer
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Application.ScreenUpdating = False
    lr1 = s1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr2
        x = 0
        For j = 1 To lr1
            If InStr(s1.Range("A" & j), s2.Range("A" & i)) > 0 Then
                If InStr(s1.Range("A" & j), s2.Range("A4")) > 0 Or InStr(s1.Range("A" & j), s2.Range("A5")) > 0 Then
                    x = x + 0
                Else: x = x + 1
                End If
            End If
        Next j
        s2.Range("B" & i) = x
    Next i
    Application.ScreenUpdating = True


End Sub

Maybe someone out there can enhance it. In my case, it either or State and Away included and excluded but cannot have it both ways.