Creating an array and selective counting of numbers across columns

[bhenderson]

Hello,


I have a spreadsheet with lots of date columns. I am trying to count 0's inbetween numbers in each row. I currently have a piece of code that will count 6 consec. 0's in a row (as 1) to help me identify patterns. It is a great start but now I need to create an array of sorts.

I need 2 pieces. One piece is an addition to my current counting code. I would like to have it only count the 0 if the date in the date column is 5 days from a non-zero number. That is kind of hard to explain so I have tried my best in the example and I dont know if it is possible but I thought someone might know a way.

2nd piece is I need to create an array output of sorts. This would count all consec. 0's in each row and create a horizontal list. My ideal breakdown is shown in the sample as well. The colors are simply to help explain not part of the intended result. The explanation is in Cell HD2

Thank you so so much for your help.

Please Login or Register  to view this content.

[bhenderson]

Sorry I didnt realize the sample attachment had been too large - it is now attached.

[rylo]

Hi

Try these.
ZeroCount2 counts all the consecutive zero items
ZeroCount3 counts the zeros that are outside the 5 day window.

Give them a good workout to make sure that things are as you expect. They do match your example output except for the 5th count of the consecutive zeros where you have 3, but the output should be 4 based on the coloring.

Please Login or Register  to view this content.

rylo

[bhenderson]

Sorry about the counting mistake and your ZeroCounter3 was exactly what I needed. Thank you so much - again!