Sumif worksheet recalc process

[cb366374]

Regarding how SUMIF recalculates...I have a large worksheet with many grouped rows (1600+) and 115k sumif formulas with a pretty crazy dependency tree. Each time an input changes or a group of rows is expanded the worksheet recalculates and it takes quite some time.

Assuming a sumif is referring to a column (call it A), checking against some criteria, and then summing column B, if I alter a cell in column B where the criteria does NOT hold true, does it still trigger a recalc? Most of the 115k sumif functions refer to a large column of numbers but few rows within that column should pass the criteria test. Based upon the performance of the spreadsheet, it appears they are all recalculating, not just the ones that the row passes the criteria. Just trying to debug this thing and hoping this information could help. Thanks in advance!

In the example below, if I change Washington to 600, will it trigger the sumif() function also?

Oregon	1400
Washington	500
Oregon	600
Idaho	2000
	=sumif(A1:A4,"Oregon",B1:B4)

<tbody>
</tbody>

 

[cb366374]

I will try to pull something together if we can't figure it out in the next day or two. It would be fairly complicated to replicate, even a scaled down version. I'll try to report back either way.

Another thing I noticed, when I remove the frozen panes, it seems to run the FastExcel "FullCalc Sheet" much faster (from 30+ seconds to 0.5 seconds). But even after removing the freeze, it still takes a while to expand/collapse groups of rows. I think there are still a lot of dependent formulas firing once the row expansion/collapse causes the recalc. Identifying those formulas has proved difficult but we're making progress. Thanks for your help thus far.