Excel - creating a range of dynamic sum formulas

Question

I am currently using the following formula

=SUM(INDIRECT("'Net "&($A$2)&" "&RIGHT($A48,2)&"'!C4:C21")) 

This works fine, but I'm trying to make part of it ( C4:C21 ) dynamic.

$A$2 , which refers to the formula, is what someone enters and currently says “May.” The $ A48 referenced by the formula simply pulls the last 2 digits of the year.

These are file fragments

In total there are 6 tabs. There are 4 tabs that correspond to sales for previous years for a specific month. So I have Net May 11, Net May 10, Net May 09, Net May 08. These are the tabs that the Indirect Formula finds.

The last two tabs include the one where this formula exists (Net May 12) and the tab on which the range should be based on (Cust May 12). On the Cust tab on May 12, column A is the day, column B is the day of the week, and column C is sales data. Someone goes every day and gains momentum throughout the day. So, for example, I currently have sales data from 5/1/2012 to 05/18/2012. Everything from 5/19/2012 to 5/31/2012 in column C is empty. The sales data that currently exists is in C4:C21 , with C22:C34 being blank. Since I am comparing sales of previous years with sales this year, they should be comparable, so the formula uses only C4:C21 for all sales for previous years.

I tried several things, including CountA and some offsets, to try to help me, but I was not able to find a solution for the dynamic range for all my sales formulas from previous years. I need the formula to simply increase to the value C4:C22 for all previous years when someone enters the next day's sales data on the Cust tab on May 12th.

Answer 1

Your question is not entirely clear, but it looks like you want something like:

 =SUM(OFFSET(INDIRECT("'Net "&($A$2)&" "&RIGHT($A48,2)&"'!C4:C4"),0,0,COUNTA('Cust May 12'!$C$4:$C$34))) 

However, you may need to restructure your data to save it in only one sheet, for example, using the columns Year, Month, Day and Sales. Then questions like “What were the sales of 2011 to date in May” are easier to answer (especially with the SUMIFS function added in Excel 2007) as follows:

 =SUMIFS(<Sales column>,<Year column>,2011,<Month column>,"May",<Date column>,"<="&DAY(NOW()))