count 1 for 15 or more consecutive 1s

[D4ND4N]

Guys,

I have a column consisting of 1s and 0s/spaces. I want to count 1 for every 15 or more consecutive 1s. The result should be the number of times of consecutive fifteen or more 1s appearing on the list. What would the function be?

Please help me on this. TY.

Regards,
D4ND4N

 

[XOR LX]

For what it's worth, here's an updated version of my last post that will count more than 15 consecutive as 1 occurance.

=SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(A1:A100)-1,0,15,1),1)=15),--(COUNTIF(OFFSET(A2,ROW(A1:A100)-1,0,15,1),1)<>15))

Non Array entered, though the use of OFFSET probably cancels out the benefit of being Non Array entered...

Probably.

Just for fun, we can abbreviate your solution to:

=SUMPRODUCT(0+(MMULT(0+(COUNTIF(OFFSET(OFFSET(A1,{0,1},,,),ROW(A1:A100)-1,0,15,1),1)-15=0),{3;5})=3))

Regards

 

[RoryA]

Just for fun

Erm, yes, right.

 

[XOR LX]

Erm, yes, right.

Ok. Point taken. I'm sad, I know.

 

[RoryA]

So am I, just in a different area. I read the VBA language spec in my spare time.

 

[XOR LX]

So am I, just in a different area. I read the VBA language spec in my spare time.

 

[D4ND4N]

Wow... Thank You Very Much to all of you Guys!!!! This site is sooo cooool!!!!! All I thought I am already good in excel but apparently I am not.. LOL.. From now onward I will be asking a lot of questions to you EXCEL MASTERS. haha. Please always help me.. Again THANK YOU GUYS...