How to get the how many times this come in this sheet

[Vishaal]

Thanks in advance,

I have following sheet
S.no. Marks Yes/no
1. 13. No
2. 16. yes
3. 18. yes
4. 19. No
5. 22 yes
6. 24. yes
7. 26. yes
8. 33. No
9. 35. No
10. 12. yes
11. 17. No
12. 38. yes
13. 40. yes
14. 42. yes
15. 44. yes
16. 46. yes

My query is I want to know how many times

1. 'continuesly three time yes' in third column - come in the above sheet

2. 'continuesly two time yes' in third column - come in the above sheet

3. 'Maximum time continues yes' in third column - comes in the above sheet

And is
1. Three time yes comes only one time
2. Two time yes comes only one time
3. Maximum time continues yes comes is 5

Please provide the VBA code or any formula for the above three point

 

[MickG]

Try this Example file:-
https://app.box.com/s/dme2apwnseb592iuhmyftz3wshz6lvaj

 

[Vishaal]

Try this Example file:-
https://app.box.com/s/dme2apwnseb592iuhmyftz3wshz6lvaj

thanks bro its working.....,

please confirm if the sheet rows increase, now its only 16 but if we increase it 16 to 40 then what changes need to be done in formula

"Yes" 2 Times

"Yes" 3 Times

Max "Yes" Times

<colgroup><col></colgroup><tbody>
</tbody>

we need to change only here

For Each Dn In nRng.Areas
If Dn.Count = 2 Then c = c + 1
If Dn.Count = 3 Then b = b + 1
oMax = Application.Max(oMax, Dn.Count)
Next Dn


[D1] = """Yes"" 2 Times": [E1] = c
[D2] = """Yes"" 3 Times": [E2] = b
[D3] = "Max ""Yes"" Times": [e3] = oMax


or any other changes will be required

 

[MickG]

You don't need to change anything the code will read down to the last row in column "C"

 

[Vishaal]

thanks bro.

 

[MickG]

You're welcome