Excel - Create dynamic sum formula range

4
votes

I am currently using the following formula

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

That works out perfectly, but I'm trying to make the range portion of it (C4:C21) dynamic.

The $A$2 that the formula is referencing is something that someone types in and currently says "May". The $A48 that the formula is referencing is simply pulling the last 2 digits of the year.

These are the pieces of the file

There are a total of 6 tabs that are significant. There are 4 tabs that total up prior years sales for a particular month. So I have Net May 11, Net May 10, Net May 09, Net May 08. These are the tabs that the Indirect formula is finding.

The last 2 tabs includes the one where this formula exist in (Net May 12) and the tab that the range will need to be based off of (Cust May 12). In the Cust May 12 tab Column A is the day, column B the day of the Week, and column C is where we have our sales data. Someone goes in every day and types in the sales for the day. So as an example I currently have sales data from 5/1/2012 until 5/18/2012. Everything from 5/19/2012 - 5/31/2012 in column C is blank. The sales data that currently exist are in C4:C21, with C22:C34 being blank. Because I am comparing prior years sales to this years sales they must be comparable, which is why the formula only uses C4:C21 for all of the prior years sales.

I have tried a few things including a CountA and some offsets to try and help me out, but I have been unable to find a solution to making the range dynamic for all of my prior years sales formulas. I need the formula to simply increment to something like C4:C22 for all of the prior years when someone types in the sales data for the next day in the Cust May 12 tab.

1
excelexcel-formulaworksheet-function

1 Answers

5
votes

Your question is not completely clear, but it sounds 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 might want to consider restructuring your data to store it in only one worksheet, say with columns like Year, Month, Day, and Sales. Then, questions like, "What were 2011's month to date sales for May," are easier to answer (particularly with the SUMIFS function added in Excel 2007) like this:

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