Most Excel users are aware that, when a formula containing relative column references is copied to further columns, those references are updated accordingly. So, for example, the formula:

=SUMIFS(C:C,$A:$A,"X",$B:$B,"X")

when dragged to the right, will become, successively:

`=SUMIFS(`**D:D**,$A:$A,"X",$B:$B,"X")

`=SUMIFS(`**E:E**,$A:$A,"X",$B:$B,"X")

etc., etc.

And so we have a relatively (no pun intended) simple means by which we can obtain a conditional sum from successive columns.

But what if the range we wish to increment is being referenced *indirectly*? For example, what if we are using a version of the above, but in which the sheet being referenced is dynamic, viz:

=SUMIFS(INDIRECT("'"&$A$1&"'!C:C"),INDIRECT("'"&$A$1&"'!A:A"),"X",INDIRECT("'"&$A$1&"'!B:B"),"Y")

where A1 contains the sheet name (e.g. “Sheet1”) which is to be referenced at any given time?