I always seem to waste time manually changing filled in series so I'm hoping
someone here has an answer:

I have a formula in A1:
=SUMPRODUCT('Sheet1'!D4:D500*'Sheet1'!$B4:$B500)

I want to have it so column B stays constant but in each column I move
across the earlier reference goes up by 3 columns:

in B1:
=SUMPRODUCT('Sheet1'!G4:G500*'Sheet1'!$B4:$B500)
in C1:
=SUMPRODUCT('Sheet1'!J4:J500*'Sheet1'!$B4:$B500)

and so on...

And if you can answer that:

I'd also like to know how to do the above if the second reference
('Sheet1'!$B4:$B500) didn't stay constant but also had to go up two columns
with each one I moved across so D4:D500 in B1, F4:F500 in C1 etc.

Any response greatly appreciated as I think knowing this would save me a lot
of time!