Excel SUMIFS - define sum_range by dynamically changing column

0
votes

I would like to make a SUMIFS formula where I can change the sum_range parametr dynamically on the grounds of this formula where I get the column: SUBSTITUTE(ADDRESS(1;MATCH("aaa";1:1;0);4);1;"")

In other words, I want to replace B:B in this formula =SUMIFS(B:B;A:A;"abc") with the formula above. But I am not able to combine those...

I found one solution here: https://stackoverflow.com/a/25814571/10452645 but I'm not quite satisfied with it. Is there another possibility to solve this task by combining SUMIFS and ADDRESS formula.

1
excelexcel-formula
Why not use an IF statement to change what the sum range is, for example =sumifs(if(A1="Yes",A:A,B:B),etc.) this is just an example - Mark S.
What you found is the best way to do this. ADDRESS and INDIRECT are volatile and should be avoided. INDEX is the quickest most solid method. - Scott Craner
If you want to make it so it is not constrained to certain columns: =SUMIFS(INDEX($1:$1048576;0;MATCH("aaa";$1:$1;0));A:A;"abc") - Scott Craner

1 Answers

1
votes

Solutions

In the above example, you can find the sum of abc from column aaa using one of the three formulas:

=SUMPRODUCT((B2:D13)*(B1:D1="aaa")*(A2:A13="abc"))

or

=SUMIFS(INDEX(B2:D13,,MATCH("aaa",B1:D1,0)),A2:A13,"abc")

or

=SUMIFS(INDIRECT(ADDRESS(2,MATCH("aaa",A1:D1,0))&":"&ADDRESS(13,MATCH("aaa",A1:D1,0))),A2:A13,"abc")

You can replace aaa and abc with a cell reference so you can "dynamically" change the SUMIFS criteria.

Please note, as mentioned by @ScottCraner

ADDRESS and INDIRECT are volatile and should be avoided. INDEX is the quickest most solid method.

Reason being a Volatile Function is one that causes recalculation of the formula in the cell where it resides every time Excel recalculates. This occurs regardless of whether the precedent data and formulas on which the formula depends have changed, or whether the formula also contains non-volatile functions. It means it could potentially slow down the calculation of your Excel workbook if it gets complicated overtime.

Having that said, choose the function that suits your preference.