Using results of formula as Sum_Range in a SUMIF

**sforney123** · 07-14-2011, 11:14 AM

I'm using Excel 2007. I have an IF statement that will return the destination of another workbook. For example =IF(E1="Jan","Auth!F:F") So if cell E1 = Jan the cell (G1 in this case) displays Auth!F:F and I want to take that Auth!F:F and input into my SUMIF statements instead of typing it in. Bascially when I change the Month in cell E1 all my formulas are automatically updated with a new Sum_Range which is being read from Cell G1 where my if statement is.

Right now my sum if is this
=SUMIFS(Auth!F:F,Auth!$A:$A,Summary!$A4,Auth!$C:$C,Summary!E$3)

I want to change the bolded sum_range to read the result of the formula in cell G1. Is there anyway to do this? I'm trying to establish a process where I only change the month and my whole spreadsheet updates. Right now I have to adjust the sum_range manually everytime I change the month.

**Miraun** · 07-14-2011, 11:23 AM

Hi Sforney;

What you're looking for is the Indirect function.

I won't lie, it's slightly complex to use, and I can't always know the full capacities of it, but it can be very powerful, especially for what you're trying to do right now.

=SUMIFS(indirect(G1),Auth!$A:$A,Summary!$A4,Auth!$C:$C,Summary!E$3)