1

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Mohemed Hussain
  • 13
  • 2

1 Answers1

0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Do you need the +1? I think =ArrayFormula(sumif(mod((row(D209:D)-row(D209)),9),0,D209:D)) would be enough? – Tom Sharpe Sep 19 '18 at 11:20