For your first question:

=SUMPRODUCT(Sheet1!$B4:$B500,INDEX(Sheet1!$B4:$Z50 0,,COLUMNS($A1:A1)*3))

Copied across as needed. Adjust for the correct last column in the INDEX
function.

For your second question:

so D4:D500 in B1, F4:F500 in C1

What would be the formula in A1?

--
Biff
Microsoft Excel MVP


"Josh Craig" wrote in message
...
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!