Changing column references as you drag down

0
votes

My Formula and columns i want to reference

=SUMIFS(Intermediate!FA:FA,Intermediate!FA:FA,1,Intermediate!ACS:ACS,1)

I am trying to create a formula that as I drag down my sheet, only the column reference changes and goes to the right. So as I drag down rows, my reference column shifts right while keeping the same data range.

1
excelexcel-formulaexcel-2016

1 Answers

0
votes

Use INDEX():

=SUMIFS(INDEX($1:$1048576,0,ROW(1:1)-1+157),INDEX($1:$1048576,0,ROW(1:1)-1+157),1,INDEX($1:$1048576,0,ROW(1:1)-1+773),1)

The ROW(1:1)-1 is the part that iterates to the next column as the row is dragged down.

The 157 and 773 are the starting column numbers for: FA and ACS respectively.

INDEX() returns the whole column as an array with the second criterion being 0 and the third criterion sets the column, using a number.

NOTE: This can also be done with OFFSET and/or INDIRECT but those are volatile and should be avoided if possible.