increase column reference when copying downwards

[littlesister]

I'm sure this question has been asked before, but I can't find anything that answers my question.

I have a formula which contains column references that I need to copy vertically, while increasing the column reference with every new copied row.

e.g. C1 contains formula:
=SUMPRODUCT(--(Report1!$X$2:$X$10806>Report1!S$2:S$10806))

Now when I copy this formula I need the next rows to show:

D1
=SUMPRODUCT(--(Report1!$Y$2:$Y$10806>Report1!S$2:S$10806))

E1
=SUMPRODUCT(--(Report1!$Z$2:$Z$10806>Report1!S$2:S$10806))

etc...

 

[Domski]

=SUMPRODUCT(--(Report1!X$2:X$10806>Report1!S$2:S$10806))

Dom

 

[littlesister]

Sorry, my mistake; I mean copying downwards:

e.g. C1 contains formula:
=SUMPRODUCT(--(Report1!$X$2:$X$10806>Report1!S$2:S$10806))

C2
=SUMPRODUCT(--(Report1!$Y$2:$Y$10806>Report1!S$2:S$10806))

C3
=SUMPRODUCT(--(Report1!$Z$2:$Z$10806>Report1!S$2:S$10806))

 

[Domski]

Not tested but try:

=SUMPRODUCT(--(OFFSET(Report1!$X$2:$X$10806,0,ROW(A1)-1)>Report1!S$2:S$10806))

Dom

 

[littlesister]

weird:

this doesn't work:
=SUMPRODUCT(--(OFFSET(Report1!$X$2:$X$10806,0,ROW(A1)-1)>Report1!S$2:S$10806))

But when I replace the ROW(A1)-1 by a digit, it works:

=SUMPRODUCT(--(OFFSET(Report1!$X$2:$X$10806,0,1)>Report1!S$2:S$10806))


Caused by any limitation within Excel?

 

[littlesister]

by the way it says: #VALUE!

 

[Domski]

Boo, I'm getting the same. I've used the same technique before for normal calculations but it just doesn't seem to like including ROW in either a SUMPRODUCT or SUM array formula.

I'm sure it must be possible, will keep looking.

Dom

 

[littlesister]

Thanks Dom.

I can always just add a column with an index (1, 2, 3, etc) and reference to the index to get the right offset, but if you find a better way, let me know!