SUMPRODUCT, SUMIF, INDIRECT and ADDRESS

Dear all,

I have a formula that looks up the sheet ref and sums based on a criteria (sales, costs, or other costs). File enclosed below.SUMPRODUCT SUMIF INDIRECT ADDRESS.xlsx

SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$4&"'!c1:c10000"),$C7,INDIRECT("'"&$A$2:$A$4&"'!f1:f10000")))

What I find frustrating is that the last part of the formula f1:f10000 does not move with the copying across the page of the formula, i.e. the columns f:f do not change to the next column of g:g.

I've tried to use address function to move 1 column along each time, but I've not had success.

Is the address option viable, or should I try another solution that uses LOOKUP and MATCH perhaps for the column reference?

Any help would be much appreciated.

Thanks.

You could use Address...
However you'd have to do it twice becaues address only returns a single cell
so you'd have 1 for F1 then concatenate ":" then another address for F10000

Try offset..

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$4&"'!c1:c10000"),$C8,OFFSET(INDIRECT("'"&$A$2:$A$4&"'!f1:f10000"),0,COLUMNS($A:A)-1)))

Dear Jonmo1,

Thank you for the great solution. It works perfectly. I had not considered using OFFSET.

Many thanks.

You're welcome.