I am trying to do a SUMIFS formula where the column I am summing (the sum\_range) will depend on the column heading matching a cell value in another sheet.

So for example, cell A2 in my sheet might say 'Actual Sales' and the formula will sum column X in my data sheet (because cell X1 = "Actual Sales"). But if I change cell A2 to 'Budgeted Sales' I want the formula to sum from column AG (so cell AG1 = "Budgeted Sales").

The other criteria in the SUMIFS formula will not change.

I managed to use an Index & Match formula when doing this on a SUMIF formula but it does not seem to work on the SUMIFS formula.

The basic formula would be as follows:

`=SUMIFS(DATA!X:X,DATA!F:F,B26,DATA!D:D,C10,DATA!E:E,DATA!C11)`

The formula above is summing column X (Actual Sales) but I want that range to be dependent on what I enter in a particular cell.

Any ideas?