I'm working on a budget and cash flow projection for a nonprofit and have gone crazy trying to pull this off...

I have one worksheet, "Staffing", that details the compensation for each of the 24 positions in the organization. The annual base salary for each position is found in cells E6-E29. For every position, there is also a "Start Date" (C6-C29) and "End Date" (D6-D29) column that stipulates the hiring month and year (in a "Jan-2013" format) for the position, and the end date if that's a temporary position.

On another worksheet, I have 1 column for every individual month from January 2013 through December 2016. Under that column, I am attempting to total all cash expenditures for that month.

Now here's the frustration...

I want to include the salary for all applicable positions under each month. In other words, if I am totaling the salary expenditures for March 2013, and I have a position that doesn't even start until January 2014, I do not want that position included (on the basis of what it has under the "Start Date" column from the staffing sheet). Similarly, if I have a position that ended in January 2013 ("End Date" column), it would no longer apply as an applicable expense for March 2013.


Can anyone PLEASE help me out here in terms of what my formula should look like? Any help would be greatly appreciated.

If I'm taking a weird approach to this, please feel free to recommend other ways of organizing and entering may data to make it easier. I just need to have, at minimum, month-level granularity for salary expenses. Day would be ideal, but for simplicity, I can get away with planning on a monthly basis.


dt