I am trying to write a formula in Excel 2007 (Windows XP, SP3). I have two spreadsheets, a master spreadsheet with purchasing data and a summarizing spreadsheet that links to it. On the summarizing spreadsheet, I am trying to tally total costs for recurring maintenance on products within a fiscal year period. On my master spreadsheet, I have a total cost column and a monthly cost column (e.g., total cost/12). I have broken out the recurring monthly costs over the months of the term in columns further to the right (e.g., Jul11, then Aug11, etc.) showing that recurring cost as it's spread over the term (typically one year). The monthly columns are grouped by color in fiscal year sets. I also have two columns in the purchase data showing when the term starts and when it ends. Unfortunately, most of the product maintenance terms span across two fiscal year periods (e.g., 5 months in FY12, 7 months in FY13). There are also multiple rows of the same product name, but with different maintenance terms and purchase dates. I'm trying to capture the monthly costs for a product (totaled on the summarizing spreadsheet) within a specific fiscal year as they spread across the sheet.
The following column descriptions may help:
Product Name: Column C (there are exact matches of product names here, but with different term dates. I need the product row that has data in any of the cells within a fiscal year data range, as shown below)
Term start date: O
Term end date: P
Total cost: X
Monthly total costs: Y (Column X/12, this amount is then spread across the columns under the months corresponding to the term dates)
Fiscal Year 2012: Spans BO:BZ (I need to total the costs shown across just these columns on a specific row with the correct product name)
Fiscal Year 2013: Spans CA:CL (Same as just above)
All of these columns described may or may not be needed to do what I'm trying to do, but I've included information about those I deem possibly relevant.
In my summarizing spreadsheet, I have a column for Product Names, FY12 and one for FY13, with each unique product name in the rows underneath where I am trying to input the formula. Once I can bring in the total costs for each of these fiscal years for all the products, I can then compare them year over year in another column.
I've been wracking my brain for two days now trying to figure out how to make this work with different formulas (Various IF, SUMIF, SUMIFS, VLOOKUP formula attempts or combinations thereof) and am close to reaching for the gun or tying the noose.
It may be that I am not even aware of the correct formula for doing what I need done.
I'm not an Excel expert and could certainly use the help of someone who is. I hope I've explained this well enough and someone can point me in the right direction.
The following column descriptions may help:
Product Name: Column C (there are exact matches of product names here, but with different term dates. I need the product row that has data in any of the cells within a fiscal year data range, as shown below)
Term start date: O
Term end date: P
Total cost: X
Monthly total costs: Y (Column X/12, this amount is then spread across the columns under the months corresponding to the term dates)
Fiscal Year 2012: Spans BO:BZ (I need to total the costs shown across just these columns on a specific row with the correct product name)
Fiscal Year 2013: Spans CA:CL (Same as just above)
All of these columns described may or may not be needed to do what I'm trying to do, but I've included information about those I deem possibly relevant.
In my summarizing spreadsheet, I have a column for Product Names, FY12 and one for FY13, with each unique product name in the rows underneath where I am trying to input the formula. Once I can bring in the total costs for each of these fiscal years for all the products, I can then compare them year over year in another column.
I've been wracking my brain for two days now trying to figure out how to make this work with different formulas (Various IF, SUMIF, SUMIFS, VLOOKUP formula attempts or combinations thereof) and am close to reaching for the gun or tying the noose.
It may be that I am not even aware of the correct formula for doing what I need done.I'm not an Excel expert and could certainly use the help of someone who is. I hope I've explained this well enough and someone can point me in the right direction.
