I cannot figure out how to use a variable range in SUMIFS. Hope someone can help.

This is the formula:

=SUMIFS($G$52:$G$122;$D$52:$D$122;$C11;$E$52:$E$122;$D11)

Or when using a table:

=SUMIFS(Details[Percentage];Details[Function];$C11;Details[Reason];$D11)

The sum range G52:G122 should be variable depending on numeric input. Input 1 (January) is range J52:J122, input 2 (February) is range is K52:K122, input 3 (March) is range L52:L122, etc

Is this possible and if so, how?

I tried to use INDEX(), MATCH(), OFFSET() but just could not get it right.

Thank you.

The formula is in column Used and now refers to %(G) in the next table. It should refer to 1 of the columns January to December, dependig on numeric input (1 to 12).

CodePudding user response：

You can use nested FILTER or INDEX function to pick column:

specify column by index:
=SUMIFS(INDEX($G$52:$R$122;0;<col_number>);$D$52:$D$122;$C11;$E$52:$E$122;$D11)

specify column by name:
=SUMIFS(FILTER($G$52:$R$122;$G$51:$R$51=<col_name>);$D$52:$D$122;$C11;$E$52:$E$122;$D11)