COUNTIF until the result is greater than or equal to a specified number.

**JSB_2011** · 01-05-2012, 01:28 AM

I have a data set that has long rows of cells that contain any number between 1-5. I need to locate the first occurence in each row whereby the number "2" occurs, but only when the following 17 adjacent cells also contain the number 2 (to make a total of 18 consecutive 1's in a row). I have been very stuck on this. Any help would be greatly appreciated!

I have attached a sample of the type of data I am looking at.

Cheers,

JSB

**JSB_2011** · 01-05-2012, 01:30 AM

Originally Posted by JSB_2011

(to make a total of 18 consecutive 1's in a row)

This is supposed to say "to make a total of 18 consecutive 2's in a row.

Sorry for the confusion

JSB

**DonkeyOte** · 01-05-2012, 02:22 AM

Perhaps:

AHS2:
=MIN(IF(COUNTIF(OFFSET($B2,0,COLUMN($B2:$AGZ2)-COLUMN($B2),1,18),2)=18,$B$1:$AGZ$1))
confirmed with CTRL + SHIFT + ENTER
copied down to AHS33

Note array entry requirement. If Array has been confirmed correctly the above formula will appear encased within { } [these can not be added manually]