Optimize SUMPRODUCT with dynamic sum range. SUMIFS??

Hi,

I have the formula:

Please Login or Register  to view this content.

The indexing part pretty much to find the column to SUM from columns DB:DY depending on a date in B2 (date headers are also in $DB$3:$DY$3).

The problem that I have, is that is the sumproduct range is already huge 24 columns by 32,530 rows. And I entered the SUMPRODUCT formula in around 25,000 cells. So it's getting real SLOWWWW to work. It calculates every time the worksheet/workbook refreshes. And it takes around 10 minutes for it to recalculate.

Is there a more efficient multi vlookup formula that also handles dynamic sum ranges. I thought of SUMIFS but, I would still need to confirm it as array due to the dynamic sum columns.

Thanks

Hello,

you can use Index/Match to define the sum range for a Sumifs. This should be way faster than the array based SumProduct. Try

Please Login or Register  to view this content.

Let me know if that works for you.

cheers, teylyn

yeah, that's exactly what I'm trying.

It's definitely faster. It's still takes like a minute or so to calculate but not the 6-10 minutes SUMPRODUCT was taking.

Thanks,

The weird thing, is that have 20 or so groups of cells. Every time I expand them, the sheet starts to calculate again. Despite the fact that it had had just re-calculated 10 seconds ago.

Any way around that?