# count consecutive cells matching string - start after 3 - ignore blanks between

#### godlev

##### New Member
i want find formula to count consecutive cells matching string (e.g. 1) but the counting of the consecutive cells to start after the 3rd consecutive entry
so if you have on a single row 5 consecutive times number 1 - the result should be 2 because the counting should start after 3rd entry
if you have 8 consecutive entries of number 1 but somewhere between them there is blank cell that should be also considered as single consecutive entry and the result should be 5

as shown on a row 9 - there are 9 consecutive times the number 1
the result is 6 because it should count only after the 3rd consecutive number 1

on row 10 is counting again only after the 3rd consecutive number 1
there are 2 consecutive entries with another number between them and it should be considered as two different consecutive entries of 1 - so the result should be 2

additionally on row number 11 are shown 2 consecutive entries with blank cell between them should be also considered as 1 consecutive entry

the numbers which the formula should count are shown below in red

 A1 B C D E F G H I J K 2 Entries Result 3 1 2 1 1 2 1 1 1 2 0 4 1 1 1 1 2 1 1 2 2 1 5 1 2 2 1 1 1 1 1 2 2 6 2 1 1 1 1 1 1 2 1 3 7 1 1 1 1 1 1 1 2 1 4 8 1 1 1 1 1 1 1 1 2 5 9 1 1 1 1 1 1 1 1 1 6 10 1 1 1 1 2 1 1 1 1 2 11 1 1 1 1 1 1 1 1 5

<tbody>
</tbody>

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
any suggestions i got stuck here

Code:
``=COUNTIFS(B3:G3,1,C3:H3,1,D3:I3,1,E3:J3,1)``

bump

Welcome to the forum.

K3: =SUM(IFERROR(SQRT(FREQUENCY(IF(B3:J3=1,COLUMN(B3:J3)),IF((B3:J3<>1)*(B3:J3<>""),COLUMN(B3:J3)))-3)^2,""))

confirmed with Control+Shift+Enter. The 1 in red (2 places) is the value you're looking for.

thank you Eric
this solves my issue and it will save me alot of manual work and manual mistakes

i just realized there is another requirement that i have to include
imagine there are 2 sheets sh1 and sh2
the counting should start from sh1 cells A1:A3
if there is 1 to continue the counting in sh2 where the whole data is as shown above

bump!

Replies
11
Views
523
Replies
3
Views
324
Replies
18
Views
469
Replies
10
Views
346
Replies
3
Views
126

1,196,409
Messages
6,015,106
Members
441,870
Latest member
kojack

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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