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?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Oregon[/TD]
[TD]1400[/TD]
[/TR]
[TR]
[TD]Washington[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Oregon[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]Idaho[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=sumif(A1:A4,"Oregon",B1:B4)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

I would expect not, but see:

Volatile Excel Functions -Decision Models

Do you have formulas with INDIRECT, OFFSET, and other volatile functions? How about array-processing formulas with SUMPRODUCT, SUM, etc?

 

[cb366374]

We actually own a few licenses of FastExcel. No volatile functions, no volatile names, no conditional formatting. There are 1600 sumproducts (one per group of rows) that are basically just summing 12 months of data if that month is marked as open (0/1 at top of worksheet).

 

[Aladin Akyurek]

We actually own a few licenses of FastExcel. No volatile functions, no volatile names, no conditional formatting. There are 1600 sumproducts (one per group of rows) that are basically just summing 12 months of data if that month is marked as open (0/1 at top of worksheet).

Care to post the SumProduct formula?

 

[cb366374]

AX91=SUMPRODUCT(AL91:AW91,$BQ$9:$CB$9)

I replaced them all with sum formulas (no arrays) and it did not improve.

 

[cb366374]

Also, for the record, I've already identified that we can reduce the number of SUMIF's by at least 80% but it will take quite some time. I was hoping there is something else I hadn't considered.

 

[Jonmo1]

It does seem to trigger a calculation when a value is changed within either range of the sumif. Even if it's in a row where the criteria is not met.

I tested by putting this code in the worksheet module.

Private Sub Worksheet_Calculate()
MsgBox "calculated"
End Sub

Using your sample table and formula, If I changed 500 to 600, i got the message box saying "calculated"

 

[Aladin Akyurek]

AX91=SUMPRODUCT(AL91:AW91,$BQ$9:$CB$9)

I replaced them all with sum formulas (no arrays) and it did not improve.

That's in fact an adequate formula. I'm thinking it's probably the sheer number formulas which might be causing the performance problem you have. SumIf(s) would not be slowed down by whole column references, unless these formulas are dependent on each other. Would it be an option to work with dynamic named ranges instead?

 

[cb366374]

There are many levels to the dependency tree that include sumif statements. There are several sections that refer to sections higher in the sheet, all of which are driven by sumif(s). So if an assumption is changed near the top it would trickle down through several sections all of which have sumif(s). However, it should only affect 1 group of rows per section (I think there are 5 sections containing ~320 groups). So it should only be a few hundred sumif(s) (115,000 total sumif(s) /320 groups =359).

I figured the SUMIF(s) either all had to be updating or there was some sort of dependency that I'm not aware of driving them all to update. Neither one is very pleasant but it's easier to solve once I know the issue.

 

[Aladin Akyurek]

There are many levels to the dependency tree that include sumif statements. There are several sections that refer to sections higher in the sheet, all of which are driven by sumif(s). So if an assumption is changed near the top it would trickle down through several sections all of which have sumif(s). However, it should only affect 1 group of rows per section (I think there are 5 sections containing ~320 groups). So it should only be a few hundred sumif(s) (115,000 total sumif(s) /320 groups =359).

I figured the SUMIF(s) either all had to be updating or there was some sort of dependency that I'm not aware of driving them all to update. Neither one is very pleasant but it's easier to solve once I know the issue.

Would it be possible to have a look at a heavily scaled-down, fake version?