0

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.

MrZH6
  • 227
  • 1
  • 5
  • 16
  • 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. Sep 05 '19 at 17:49
  • 1
    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 Sep 05 '19 at 18:17
  • 1
    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 Sep 05 '19 at 18:19

1 Answers1

1

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.

Terry W
  • 3,199
  • 2
  • 8
  • 24
  • I have tested all three formulas. All of them work perfectly. I had a little bit of hard time with the last one as I have the source data on another worksheet. Also, I feel not very comfortable with the `INDIRECT` function... Anyway, thank you very much @ScottCraner and @TerryW – MrZH6 Sep 06 '19 at 13:33