So I have an excel spreadsheet indicator and I am trying to make a SUMIFS statement that will change which column I am selecting from. So I will have the column name on the left and I want to use that as a varying parameter. See the below tables.
TABLE1
week1 week2 week3
column1 x x x
column2 x x x
column3 x x x
TABLE2
Week column1 column2 column3
1 x x x
1 x x x
1 x x x
2 x x x
2 x x x
2 x x x
So in the above tables TABLE2 holds all the data and TABLE1 is basically a sum of the data by week. So I want the SUMIFS to read something like SUMIFS(TABLE2[????], TABLE2[Week], $B$2) But I can't figure out how to get the sum_range part of the SUMIFS to let me pass parameters in. Any tips or ideas on how to do this?
=INDIRECT("TABLE2[" & A3 & "]")
will resolve to TABLE2[column1] (assuming A3 holds the text "column1")
so the indirect function could be put in as your sum_range (tested in excel 2007)