VBA Count

[anthonyexcel]

I have data that I am trying to count, I need to count Count rows that have Q1, Q2,Q3, and Q4 in it. The code below works for 1 variable i.e.(Q1) and it works but I need an update for All of the variables. Count I am looking for is 7. Thanks in advance!

Q1,Q2,Q3,Q4 Q1,Q2,Y1 Q1,Q2,Q3,Y1 Q1,Q2,Q3,Q4,Y1 Q1,Q2,Q3,Q4,Y1 Q1,Q2,Q3,Q4,Y1 Q1,Q2,Q3,Q4,Y1 Q1,Q2,Q3,Q4,Y1 Q1,Q2,Y1 Y1 Q1,Y1 Q1,Y1 Q2 Q1,Q2,Y1 Q1,Q2,Q3,Y1 Q2,Q3,Q4 Q1,Q2,Q3,Q4 Q2,Q3,Q4 Q2,Q3,Q4 Q2,Q3,Q4 Q2,Q3,Q4 Function CountQuarters(r As Range, quart As String) Dim d As Double Dim parts() As String For Each xcell In r parts = Split(xcell.Value, ",") For Each p In parts If InStr(p, quart) > 0 Then d = d + 1 End If Next Next CountQuarters = d End Function

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1
2	Q1,Q2,Q3,Q4		7
3	Q1,Q2,Y1
4	Q1,Q2,Q3,Y1
5	Q1,Q2,Q3,Q4,Y1
6	Q1,Q2,Q3,Q4,Y1
7	Q1,Q2,Q3,Q4,Y1
8	Q1,Q2,Q3,Q4,Y1
9	Q1,Q2,Q3,Q4,Y1
10	Q1,Q2,Y1
11	Y1
12	Q1,Y1
13	Q1,Y1
14	Q2
15	Q1,Q2,Y1
16	Q1,Q2,Q3,Y1
17	Q2,Q3,Q4
18	Q1,Q2,Q3,Q4
19	Q2,Q3,Q4
20	Q2,Q3,Q4
21	Q2,Q3,Q4
22	Q2,Q3,Q4
23
Lists

Cell Formulas
Range	Formula
C2	C2	=COUNTIFS(A:A,"*Q1,Q2,Q3,Q4*")

 

[anthonyexcel]

Yep that works Fluff, thanks for giving me insight!!!!

Function xquarters(r As Range)
Dim xcell As Range
    For Each xcell In r
        If xcell.Value Like "*Q1,Q2,Q3,Q4*" Then
            i = i + 1
        End If
    Next
    xquarters = i
End Function

 

[Fluff]

You're welcome & thanks for the feedback.