Search code examples
google-sheetsarray-formulasgoogle-sheets-formulamodular-arithmetic

Formula that counts every 9th row

I need a formula that counts every 9th row in the D column starting with D209. So far I have:

=sumif(ArrayFormula(mod((row(D1:D)-row(D1)+1),9)),0,D1:D)

The formula works right by counting every 9th row but I'm having a hard time getting the formula to start at D209.

Solution

  • Please try:

    =ArrayFormula(sumif(mod((row(D209:D)-row(D209)+1),9),0,D209:D))+D209

    The intent of +1 and +D209 is to count, but not double count, the value in that cell†. There may be other (and better!) ways to achieve the same result but I aimed to minimise the difference between the formula above and the OP's attempt (except I moved ArrayFormula to the outside as that may be better practice in general). Minimising differences can help OPs to understand what it was they got wrong (though I admit the reason here was me being lazy, as usual).

    † Off-by-one error may be of interest. Whereas the indices of "every 9th" would mostly be multiples of 9 the first entry would be index 1 (1-based), as I understand the requirement.