Count number of cells between certain paramaters

[Gerhardstru]

HI

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
3	1	2	3	1	3	1	5	d/o	4	8	6	4	1	2	d/o	1	5	6	d/o	1	2	8	7	8	7

<tbody>
</tbody>

Can you please assist me with a formula to count the following; I need to know how many times there are more than or 5 entries between "D/O"

In the example above the answer should be 3. I.E. a-g = once, i-n = once and t-y = once

Thanks

Gerhard

 

[pgc01]

Hi Gerhard

Try:

=SUM(--(FREQUENCY(IF(A3:Y3<>"d/o",COLUMN(A3:Y3)),IF(A3:Y3="d/o",COLUMN(A3:Y3)))>=5))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

 

[Gerhardstru]

Hi Gerhard

Try:

=SUM(--(FREQUENCY(IF(A3:Y3<>"d/o",COLUMN(A3:Y3)),IF(A3:Y3="d/o",COLUMN(A3:Y3)))>=5))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Thank you, did a test on a small sample and it is working. Only change >=5 to >5 Will check on the main data source. If I have issues I will be back

Thanks Again

 

[pgc01]

You're welcome.

 

[bhos123]

Sub macro1()


Dim x As Long
Dim y As Long
Dim z As Long
z = 0
y = 0
Sheets("Sheet3").Select
For x = 1 To 100
If Cells(1, x).Value = "d/o" Then
y = x - y
If y >= 5 Then
z = z + 1
End If
End If
Next

Cells(2, 1).Value = z

End Sub