I have rows of records, each w/ a start and end date. How can I compile and summarize these in a separate table, month by month

[jamwil]

Above I have a list of projects, each on a new row, each with a start and end date. I need to take this information and organize it into a month by month forecast, the outline of which should look something like this:

Is this possible or reasonable to do? I've looked into vlookup(), match() and offset()—it seems these functions may hold the key but it's a very complex task and I'm not really sure where to start. Is there a function (or set of functions) that's better suited for this kind of work? How would you tackle it?
Thanks,
James

(Excel 2013 / Windows 8)

 

[Akashwani]

Hi James and welcome to MrExcel.

Based on your first table, I cannot understand what it is you would like to display in your second table!

For example, look at my table below and tell me what you would like to see displayed for March.....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1			Inventory			Forecast
2	Fiscal Year	Month	Raw	WIP	Finished	Baseline	Optimal	Best Case
3	2013	March
4		April
5		May
6		June
7		July
8		August
9		September
10		October
11		November
12		December
13	2014	January
14		February
15		March
16		April
17		May
18		June
19
Sheet1

And where is this data to come from?

Ak

 

[jamwil]

Thanks for the quick reply! Here's a .xlsx if it helps: DummyForecastFY2014.xlsx

I'll give you an example of what I'm looking to do. Let's say I need a forecast for the month of May 2013 (I need it for every month listed but we'll start with one)... If I go on the 'month by month' worksheet, on the May row, I need it to look through the list of projects, pick which ones are ongoing during that month (based on the start/end date of each project), then put together the forecasts. The Baseline forecast is simply the number of units Closed plus the number of units Committed to in the given month. The Optimal and Best Case forecasts incorporate the number of units in the pipeline, just with a different threshold for 'health' (the likelihood of success). I hope that makes sense.

Thanks for the warm welcome.

James

 

[Akashwani]

Hi James,

I cannot access that file as I'm not a member of that forum.
If you have posted this question elsewhere then you should reference that question in your post!!
That way people will not be wasting their time when someone else may have solved it elsewhere!!!

Considering the above, I cannot help you further with this.

Good luck.

Ak

 

[jamwil]

Apologies—I assure you it has not been solved; I would let all parties know if it was and I most definitely have no intention of wasting your time. I have none to waste of my own. I've found Mr. Excel to be better (having just lurked in the past) than ExcelHelp, but I posted the question to both boards as a function of due diligence. You can find the other post here: Rows of records, each w/ start & end date. Need to compile and summarize month by month

Here is a copy of the excel file hosted on my dropbox which should be accessible by all: https://dl.dropbox.com/u/144658904/DummyForecastFY2014.xlsx

 

[jamwil]

Okay I got it! The trick was to use SUMIFS. For any given month, you run SUMIFS testing that the start date is at or prior to that month, and the end date is at or after that month. Repeat for each month and you find yourself with a month by month summary.