Counting the Spans of Zeros between non-zero values

[cewolfe]

I have data of the monthly total of purchases by each customer. It looks something like this: 0,0,0,100,200,0,0,300,0,0,0,20,0,250,0,0. All in separate cells in a row. I want to be able to return the values of 2,3,1. I need to find a way to count the amount of zero values between the non zero values. With or without the amount of zeros following the last nonzero number is a little less important. For reference I have a formula to count to the amount of months since the first purchase and also the amount of months since the last purchase. They are the following: =COUNT(DD2:INDEX(A2:DD2,MATCH("",T(1/A2:DD2),0))) and =ROWS(A2:DD2)-MATCH(2,1/(A2:DD2>0))+108. Any Help would be greatly appreciated. Thanks PS using Excel 2010 I believe

[cewolfe]

Try the array formula below in DL2. Confirm it with Ctrl + Shift + Enter instead of the regular Enter. Fill down through all of your rows, then fill right as far as you need to. In each column it should list the size of the gap between non-zero entries. See attached for (hopefully) more clarity:

 =IFERROR(SMALL(IF($G2:$DJ2>0,COLUMN($G2:$DJ2)),COLUMN(A:A)+1)-SMALL(IF($G2:$DJ2>0,COLUMN($G2:$DJ2)),COLUMN(A:A))-1,"")

 The results I've checked appear to hold up?

[patio]

Did you just answer your own question ? ?

Hope you get it figured out though...

[cewolfe]

Yeah... Another forum helped me out first so I just copy and pasted the answer because I wanted to share for anyone that might need it but don't care about being perfect

[patio]

Good idea...Welcome Aboard .