Drag formula to every X rows

Question

I'm trying to apply formula to every forth row in a column:

=SUMPRODUCT('Sheet1'!C$2:C$25;TRANSPOSE('Sheet2'!$E2:$AB2))

and point is that rows in range for 'Sheet2' have to add 1 row, like:

=SUMPRODUCT('Sheet1'!C$2:C$25;TRANSPOSE('Sheet2'!$E3:$AB3))

but system add 4 rows every time i drag or paste the formula:

=SUMPRODUCT('Sheet1'!C$2:C$25;TRANSPOSE('Sheet2'!$E6:$AB6))
=SUMPRODUCT('Sheet1'!C$2:C$25;TRANSPOSE('Sheet2'!$E10:$AB10))

pls help

Answer 1

Here's one approach added to your sheet which you may test out:

=reduce(tocol(;1);B2:index(B:B;match(;0/(B:B<>"")));lambda(a;c;vstack(a;if(c="";tocol(;1);reduce(torow(;1);sequence(12);lambda(f;q;hstack(f;
 let(w;sumproduct(choosecols('materials / work'!C2:N49;q);tocol(filter(production!C:AX;production!B:B=c)));x;if(w=0;;w);y;offset(c;;2);z;y-x;Σ;iferror(z/y);Λ;iferror(z/x);
 vstack(x;z;Σ;Λ)))))))))