IF / Countif / Sumif - Formula help please

Hi Chris,

In attached file is the prototype, results looks like

Instead of 26 you may put any number greater than 3 (number of rows above your data).

Calculations are added into the column F in parallel to your data where i added one more YES in yellow. Here is an assumption - you have to have the size of your cycle (26) somewhere in first rows of the column F, otherwise the logic of the formula is to be changed.

To generate the counter i used

=ROUND(MOD(<prev value>+0.7,26),0)

0.7 here is any one between 0.5 and 1 to round the result to next integer, you may use ROUNDUP as well.

To find the previous value for the counter we check for the current cell

OFFSET(F4,-1,0)

If it's not zero we take that value, if zero we find the latest non-zero value in the upper region with max 26 height size, or reduce the size if the current row is less than 26 to avoid the error generation. This part of the formula finds in reverse order the position of such value in above region

INDEX(MATCH(2,(--1/OFFSET(F4,-MIN(ROW()-1,$F$2),0,MIN(ROW()-1,$F$2))<>0))

After that we offset on this position and pickup the value of the previous counter

OFFSET(F4,
   -MIN(ROW(),$F$2)-(ROW()>$F$2)+INDEX(MATCH(2,(--1/OFFSET(F4,-MIN(ROW()-1,$F$2),0,MIN(ROW()-1,$F$2))<>0)),)
,0)

MIN gives the size of the region depends on current row position.

The correction

-(ROW()>$F$2)

is to add one more position when row number begins bigger than counter size. Perhaps could be done another way, but i didn't dig.

Entire formula for F4 is

=IF(B4="YES",
    0,
    ROUND(MOD(
        IF(OFFSET(F4,-1,0)=0,
            OFFSET(F4,
                -MIN(ROW(),$F$2)-(ROW()>$F$2)+INDEX(MATCH(2,--(1/OFFSET(F4,-MIN(ROW()-1,$F$2),0,MIN(ROW()-1,$F$2))<>0)),),0),
            OFFSET(F4,-1,0)
        )+0.7,
        $F$2
    ),0
    )
)

copy it down to another cells. And finally apply custom format to them

0;;;@,

which shows zeroes as empty cells and the rest as integers.