A count on a repeating pattern every 14 cells
 

I'm trying to do a graph from some data I have. The Graph is easy but the data part is hard. I am able to count the data I want using this formula

=SUM(Teamdata!$B$9:Teamdata!B9)/COUNTIF(Teamdata!$B$9:Teamdata!B9,"0")

but I realized my data will be skewed but I have some zero's that I do not want to count. There is a repeating pattern every 14 cells. Using Cell A1 for the start, this is what it looks like.

A1 - count
A2 - count
A3 - count
A4 - 0
A5 - 0
A6 - count
A7 - count
A8 - 0
A9 - 0
A10 - 0
A11 - count
A12 - count
A13 - 0
A14 - 0

The ones that say count is what I want to count and is the repeating pattern I have every 14 cells. The ones that are zero will show up as zero's but I don't want to count. I will have cases where the count cell is zero but I still need to count the cell because it's part of a running average.

Try this one via a Scroll Bar in Form Controls.

The Scroll Bar should have the Page Change value of 14 (and do not change anything else) then the cell link could be anywhere. Let's use B1 as the cell link and your table as an example.

B2 should have the formula =SUM(OFFSET(A1,B1,0,14,1))/7

As you move your scroll bar, the range of the formula moves as well.