Based on the sample alone there would not appear to be any Volatility in the model.
Assuming you need to persist with SUMPRODUCT for sake of backwards compatibility (assuming saved as .xls):
1. add a pre-emptive IF to avoid calculating the SUMPRODUCT unnecessarily, eg:
2. consider using Dynamic Named Ranges re: WI sheet so as to avoid the need for excess capacity - though use an INDEX based construct rather than OFFSET (previously mentioned Volatility issues)
effect of this will however be marginal depdending on volume of spare capactity and offset by overhead in calculating the DNR
3. consider creating a key on WI sheet so you can avoid SUMPRODUCT and revert to SUMIF, eg:
Your summary calc then becomes:
though less elegant this will be significantly more efficient than the approach you are presently using
(to the extent that you could also use entire column references in the SUMIF if preferred)
I can not stress enough just how significant the impact of 3. would be on the performance of your model !
However, if backwards compatibility is not a concern then use SUMIFS (like SUMIF this will be significantly more efficient)
Bookmarks