Tally a specific span of cells on a specific row to another worksheet

GBL

New Member
Joined
Apr 3, 2013
Messages
1
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,222,310
Messages
6,165,265
Members
451,949
Latest member
bovacik

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top