Excel 2007 : Counting consecutive blank cells for multiple rows from various starting points

[JMcC421]

I have a series of data that includes months going across the columns and in the rows are accounts. The cells are populated with the amount of sales for each account during each month. I am trying to find a formula to quickly note which accounts have more than six month of inactivity or "0" sales. However, the catch is that I need the search to begin with the first month that each account begins its sales. For example, one account can have its data beginning in March and another account can have its sales start in June. I need the search of consecutive 0 value cells to start from each account's first month of sales. The formula should return a value, such as "0" for accounts that do not have 6 consecutive months of 0 sales at any point since their first month of sales or "1" for accounts that do have 6 consecutive months of 0 sales at any point since their first month of sales. I've included a sample attachment. Thanks!

[tigeravatar]

JMcC421,

Welcome to the forum!
Easiest way I know of to do that would be to use helper columns (which can be hidden). Attached is a modified version of your posted workbook.

In cell AA2 and copied over and to AQ2, and then copied down for the number of rows with data (so down to row 11) is this formula:

=IF(COUNTIF($B2:B2,">0"),IF(B2=0,Z2+1,0),0)

That formula will finds all consecutive 0's after the first month with a value.
Then, to determine if there are 6 consecutive 0's, in cell S2 and copied down is this formula:

=IF(COUNTIF(AA2:AQ2,6)>0,1,0)

Does that work for you?

[JMcC421]

Thank you so much! That looks like it works! And has just saved me tons of time!!

[martindwilson]

this might wotk but i haven't tested all conditions just all those i could think of

[JBeaucaire]

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.