Time-based cash flow question.

**dtrimble** · 10-26-2011, 08:33 PM

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

**tuph** · 10-26-2011, 10:16 PM

If you attach a sample of your workbook it will be easier to make some recommendations. Be sure to replace any confidential information with dummy details before uploading it.

**dtrimble** · 10-27-2011, 01:09 AM

Originally Posted by tuph

If you attach a sample of your workbook it will be easier to make some recommendations. Be sure to replace any confidential information with dummy details before uploading it.

Attached in XLSX. Stripped out everything but the two sheets in question, and modified the data to use fake numbers, positions, etc.

If you look at the Staffing worksheet, you'll see each position along with a start and end date for each (or blank for positions with no end date), and an annualized salary.

If you look at the Expenses worksheet, you'll see a row for "Base Salaries", with columns for every month from Jan 2013 through Dec 2016.

Under each month for Expenses on the Base Salaries row, I want to include 1/12 (monthly) of the annualized salary for every position on the Staffing worksheet, IF the position has a Start Date equal to OR later than the month being summarized, and excluding positions that have an End Date earlier than the month being summarized.

No need to worry about the other rows on expenses; I'm just interested in tallying the base salaries and tying it to a hiring timetable.

Dan

**brokenbiscuits** · 10-27-2011, 06:42 AM

See attached.

As you're using 2010, there would be a tidier way of doing this using the SUMIFS function; however I don't have access to 2007 today to test it so you're stuck with SUMPRODUCT unless someone else can offer further help.

It still works though!

**dtrimble** · 10-27-2011, 11:00 AM

Originally Posted by brokenbiscuits

See attached.

As you're using 2010, there would be a tidier way of doing this using the SUMIFS function; however I don't have access to 2007 today to test it so you're stuck with SUMPRODUCT unless someone else can offer further help.

It still works though!

This is great, thanks!

A couple quick questions:

1. What are the advantages of using SUMIFS rather than SUMPRODUCT?

2. In the formula:

=(SUMPRODUCT(--(Staffing!$C$6:$C$29<=Expenses!C$8),--(Staffing!$D$6:$D$29>=Expenses!C$8),--(Staffing!$E$6:$E$29))+SUMPRODUCT(--(Staffing!$C$6:$C$29<=Expenses!C$8),--(Staffing!$D$6:$D$29=""),--(Staffing!$E$6:$E$29)))/12

...you use two hyphens in several spots. I'm not familiar with the use of hyphens in this manner...can you clarify what this is doing?

**brokenbiscuits** · 10-27-2011, 12:11 PM

1. SUMIFS is a generally easier to understand syntax. It's a logical extension of the SUMIF function. Further to that, it calculates faster (noticeably so on a large spreadsheet) than the equivalent SUMPRODUCT. On the other hand, SUMPRODUCT works on closed workbooks, which I believe SUMIFS doesn't.

2. The double negation (--) is used when you are obtaining TRUE/FALSE values, to ensure Excel treats them as 1 and 0 respectively. They were unnecessary on the last section, --(Staffing!$E$6:$E$29), and can be removed (I just put them in out of habit, they can't do any harm!), because this part of the equation doesn't check to see if something is true or false.

It can be hard to get your head around, which is why the simpler SUMIFS might be a better bet, but theres a good simple tutorial here which might help:

http://techtites.com/2008/05/22/exce...le-conditions/

Anyway, glad it works for you!