Too many sumproduct formulas causing slowdown

[sd7000]

Hi all,

First, I want to say thanks for any help you might be able to offer. Attached are 2 sheets out of a workbook I am using which has many sumproduct formulas. On the the Input sheet, is where vacation and training forecast dates are listed. On the Calcs sheet, there are 2 monthly calendars for the year, 1 for vacation, the other for training. Would like to know if there is a way to have both records show up on only one calendar set. I currently have the vacation shown as a '1' and the training shown as a '9'. The workbook is used in Excel 2003 & 2007.
Thank you, Steve

[DonkeyOte]

To be honest given your layout and the restriction of XL2003 compatability you will struggle ... the best approach in these instances is IMO to store the date grid adjacent to each transaction and populate for each transaction (very simple formula) and this in turn makes aggregation a very simple and inexpensive task (ie using SUMIF), however, the requirement of 365 days means this is not viable in XL2003 (limited to 256 columns less those required for data entry).... you could possibly have different sheets for each qtr and do a 3d summation but it seems like overkill.

[DonkeyOte]

Here's an alternative approach, see Input2 / Calc2

This is based on one key requirement - that you split any absences that cross months into separate lines (an example of which is highlighted in yellow on Input2)

Doing this allows you to be a little "creative" in so far as you need only have columns per day of month (ie max of 31) and can create a concatenation of employee & month in one column (E) ... this means that on final calendar you can use a basic SUMIF which will be far more efficient than a SUMPRODUCT / Array based approach.

I have only setup the first few months for the latter approach - you can obviously replicate remaining months if you choose to adopt this approach.

even if you choose not to adopt it will hopefully give you some ideas as to possible workarounds....

[sd7000]

Thank you so much for the quick reply and another idea on how to solve my issue, I will definitely pursue it. You mentioned that with XL 2003 I would struggle due to the limitations, if it was done only for XL 2007, would there be a different direction to go?
Thanks, Steve

[DonkeyOte]

...would there be a different direction to go?

Well using your original setup you could possibly look at using SUMIFS rather than SUMPRODUCT - the former being more efficient than the latter but unlike the latter SUMIFS is not backwards compatible with earlier version of XL.

Given the greater number of columns available in XL2007 you could also store each of your 365/6 days as a separate column adjacent to each transaction - the only advantage of this over my previous attachment is that you need not split transactions that cross over multiple time periods ... that said I think the prior proposal is still a good way to go (you just need to remember to do the transaction split as and when required).

[sd7000]

Thank you for your assistance.