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

d/o

d/o

d/o

<tbody>
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]

</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