# Formula To Count Consectives.

#### CARBOB

##### Well-known Member
I need 2 formulas. The Digits in Col's B:F, range from 1-36, In Col G, I would like a formula that will return a Y(Yes) or N(No). Y if any of the digits are consective, N if none. In Col H, I would like a formula that will count the number of Consectives. Ex if B:F has 25,26, return a 2; 25,26,27, return a 3. Can this be done. All suggestions appreciated.
Thanks!!!
EL FANTASY 5 REPEATS.xlsm
BCDEFGH
32227283336Y2
40612181920Y3
50213161826N0
61924313233
70711212635
IMPORTED FILES

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Domenic

##### MrExcel MVP
Provided that the numbers are listed in ascending order, as per the sample data, try...

G3, copied down:

=IF(SUMPRODUCT(--(C3:F3-B3:E3=1))>0,"Y","N")

H3, copied down:

=CHOOSE(SUMPRODUCT(--(C3:F3-B3:E3=1))+1,0,2,3,4,5)

Hope this helps!

#### DonkeyOte

##### MrExcel MVP
calculate consecutives first and then if > 0 use Y else N

simple way to do this would be to use an IF in Col H

=IF(C2=B2,1,0)+IF(D2=C2,1,0)+IF(E2=D2,1,0) and so forth for all cells... that would give you total number of consecutives

in col G =IF(H2>0,"Y","N")

EDIT: Domenic - NICE!

#### CARBOB

##### Well-known Member
Thank you!!! Very Good!!

Provided that the numbers are listed in ascending order, as per the sample data, try...

G3, copied down:

=IF(SUMPRODUCT(--(C3:F3-B3:E3=1))>0,"Y","N")

H3, copied down:

=CHOOSE(SUMPRODUCT(--(C3:F3-B3:E3=1))+1,0,2,3,4,5)

Hope this helps!

Replies
13
Views
479
Replies
9
Views
244
Replies
3
Views
85
Replies
17
Views
361
Replies
0
Views
115

1,191,199
Messages
5,985,233
Members
439,952
Latest member
djharter

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back