# Count consecutive missing values in columns

#### smide

##### Board Regular
Hello.

In columns A and B (A3:B802) I have a list of 25 different products (Product 1 - Product 25) with their corresponding 'markers' in columns C and D (when Product is in column A his 'marker' is in the same row in column C, and when Product is in column B his 'marker' is in the same row but now in column D).
Each Product's marker can have a value of zero, one or three (0,1,3).

What I need to find is to COUNT how many consecutive times each Product appears without marker 3 relative to last Product appearance eg. referent cell (columns A or B).

Data in row 3 are actually the last one (from which counting starts).

example. (for simplicity here only for Product 1)

Results are in column E.

 A B C D E 1 2 Marker A Marker B Results 3 Product 1 Product 3 1 0 4 Product 4 Product 1 3 2 5 Product 3 Product 6 6 Product 1 Product 4 0 1 7 Product 7 Product 1 1 0 8 Product 8 Product 6 9 Product 1 Product 2 3 .... 10 .... .... 11 .... .....

<tbody>
</tbody>

Results: E3 = 0, because next Product 1 (B4) marker (D4) already contains number 3

E4 = 2, because there are two Product 1 markers less than 3 before marker with number 3 for Product 1 appears in row 9

E6 = 1, there is only one Product 1 marker less than 3 before marker with number 3 for Product 1 appears in row 9

E7 = 0, similar story as for E3

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Eric W

##### MrExcel MVP
I'm sure this can be simplified, but here's a first stab at it:

ABCDEFG
1Product
2Marker AMarker BResultsProduct 1
3Product 1Product 310
4Product 4Product 132
5Product 3Product 6
6Product 1Product 401
7Product 7Product 110
8Product 8Product 6
9Product 1Product 230
10

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
E3{=IF(COUNTIF(\$A3:\$B3,\$G\$2),SUM(IF((\$A4:\$B15=\$G\$2)*ISNUMBER(\$C4:\$D15)*(ROW(\$A4:\$B15)< IFERROR(1/(1/MIN(IF((\$A4:\$B15=\$G\$2)*(\$C4:\$D15=3),ROW(\$A4:\$A15)))),ROW(\$A\$15))),1)),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I used 15 as the maximum row of your range, which you'll need to change.

Last edited:

Replies
5
Views
278
Replies
1
Views
176
Replies
3
Views
991
Replies
1
Views
160
Replies
0
Views
356

1,190,913
Messages
5,983,538
Members
439,848
Latest member
timmyo

### 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