Sumif with a moving sumrange

1
votes

I have data, in corresponding columns right next to each other like : total in column n and hours in column o, I need a formula that changes my sumrange in my sumif formula.

how to change the referring column for the sum range in a sum if formula based on the column name being selected from a drop down list?

2
excelexcel-formulaexcel-2010

2 Answers

2
votes

I believe you're looking for the "offset" formula.

The sumif formula would look like this:

sumif(column range1, criteria, offset(column range2, , reference offset))

reference offset would be cell a1 and contain the number of columns you want the formula to move. So if a1 = 0. Your oringial column range2 would be the summed cells. If a1 is changed to = 1, the formula would sum one more column over, to column range2 + 1.

1
votes

If you want to base the sum range on the column header in row 1 as per your question then perhaps use INDEX/MATCH like this

=SUMIF(A:A,"x",INDEX(B:Z,0,MATCH("name",B1:Z1,0)))

That will do a sumif for value "x" in col A with the sum range being the column with "name" in row 1