Sumproduct-not all employees are working

[Debs1]

I have two databases.

1. List of employees with the following columns, name, cost center, start date, end date, and annual salary
2. I have another template which should calculate the monthly salary for each cost center

I can do a sum if of each cost center and divide by 12.

BUT not all employees are working in each month.

How can I write a sumif/sumproduct formulae that only includes each employees amount if the employee is working in the current month.

eg if I am looking at Jan 10, only make a sumif of employees for that cost center who have a start end date including Jan 10

[JeanRage]

Hi,

Ideally a sample worksheet would allow forum participants to write the exact formula needed for your problem ...

HTH

[Debs1]

Thanks Jean,

here is the attachment.

Debbie

[JeanRage]

Hi Debbie,

For your monthly totals, what prevents you from simply adding all the salaries of any given month ...?

[DonkeyOte]

Based on your sample something like:

Please Login or Register  to view this content.

word to the wise though this approach will prove hideously inefficient on large data sets and or when used in large volume (no. of calcs)

long term if would be better to create the requisite monthly matrix adjacent to the salary database using more efficient (basic) formula to generate totals per row (ee) in the appropriate months
the cc summary sheet would thus become a basic SUMIF function which will be significantly more efficient
(so in short more less expensive formulae will lead to better performance long term)

[JeanRage]

Hi Debbie,

Attached is a sample file for you ...

HTH

[DonkeyOte]

JR, I believe the aim here is to generate a salary forecast covering a 15 month period with salary costs apportioned on a monthly basis as per utilisation (as dictated by start/end dates of employment).

[DonkeyOte]

For the sake of clarity I figured I'd upload an illustration of my suggestion regards conducting the "hard work" on the source sheet rather than the summary sheet given this will in effect prove more efficient long term.
This approach also provides far greater flexibility should you decide latterly than you need to apportion salaries within any given month (ie mid month starter costs 1/2 normal cost) or if you decide you want to forecast salary raises at a given point in time etc etc...

On the attached the cells in yellow represent those you would change manually - ie on Summary sheet - Cost Centre and start month of forecast... changing the latter would update the results matrix on the Data sheet automatically thus ensuring your results reflect the period being forecast.

I hope it helps.

[Debs1]

Thanks to you both. Donkeyote you have interpreted my request correctly. I tried the SUMPRODUCT formulae that you provided and it did not work. I got an error message and the cursor flasged on $c$33. I tried to make some changes reading the suggested reading material, and the best success I got, was no error message but only 0 showing as my answer.

Thankyou for recommending that we should first calculate the monthly amounts and then do a sumif. This is what we do today. The issue is that this works find when we are looking at either a 12mth budget, or a March reforecast, or a June Reforecast etc. The challenge I now face is to build a model which includes 2010 Budget, March R/F, June R/F, Sept R/F and 2011 budget. I wanted to try and keep just 5 separate updated annual salary columns without having to phase each of them and end up with too many columns out to the right. In addition we have columns to the right with a section showing by month benefits, and another section for bonus's, and another section for HC and another for FTE....so i would simply run out of columns, and therefore need to find an alternative solution.

I will attach an illustration in the next tab, as for some reason I cannot attach one to this message.

[JeanRage]

JR, I believe the aim here is to generate a salary forecast covering a 15 month period with salary costs apportioned on a monthly basis as per utilisation (as dictated by start/end dates of employment).

Honestly ... from the initial post, I had not guessed it ... !!!
Cheers

[DonkeyOte]

The challenge I now face is to build a model which includes 2010 Budget, March R/F, June R/F, Sept R/F and 2011 budget. I wanted to try and keep just 5 separate updated annual salary columns without having to phase each of them and end up with too many columns out to the right. In addition we have columns to the right with a section showing by month benefits, and another section for bonus's, and another section for HC and another for FTE....so i would simply run out of columns, and therefore need to find an alternative solution.

I hear what you're saying but I can tell you from [painful] experience that a SUMPRODUCT based approach, ie calculating in aggregate, will have a severe impact on performance -- so much so that I would hedge my bets that the model itself will pretty much grind to a halt and thus become virtually unworkable. Furthermore conducting benefit based calcs in aggregate can be very (very, very) complex ... especially if those calcs are specific to an individuals history (ie join date) and given point in time.

As ludicrous as it may sound I would suggest that you recreate your existing sheet five times over ie one for each forecast / budget that is to be reported

I agree this may sound utterly insane - ie quadruple increase in count of formulae, however, I would pretty much guarantee that it will be still be more efficient than the alternative ... in native XL elegance and efficiency do not always go hand-in-hand - lots of basic formulae will calculate far quicker than a lesser number of Array / Sumproducts - all boils down to how the Sumproduct / Array formulae are processed.