complex custom fill series with formula

[Josh Craig]

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'!D4500*'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 D4500 in B1, F4:F500 in C1 etc.

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

 

[T. Valko]

For your first question:

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

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

For your second question:

so D4500 in B1, F4:F500 in C1

What would be the formula in A1?

 

[Josh Craig]

Hi Biff,

Just about to check if your first one works...

For the second one assume A1 is the same as in the first example (except no
$ for the Bs)

 

[Josh Craig]

Hi Biff,

I got the first one to work but I had to replace Z with a much higher column
letters (DD). Do you know what the maximum possible column letters would be
so I can put that in?

 

[T. Valko]

It depends on what version of Excel you're using. If you're not using every
column it's a waste of resouces to include those that are not actually being
used.

Excel 2007 last column XFD
All other versions IV

I'm still not sure about your second question. Tell me specifically what
columns the formula in cell A1 should reference.

I'm calling it a day so someone else will have to take over. If not I'll
check back tomorrow.