Solved: sumproduct freezing the file

[tkaplan]

I have a workbook in excel 2007 with 2 spreadsheets. sheet1 has approximately 4000 records with 4 of the columns being named ranges and one of the columns just a simple countif formula.

the second tab is a grid of 6 columns and 12 rows of sumproduct formulas, referencing the named ranges.

I have had workbooks that were much larger and had more formulas than this, but for some reason, this workbook takes a tremendous amount of time to calculate. I tried doing this again from scratch, but it's still not working.

any advice would be greatly appreciated. I am attaching the workbook.

TIA
~t

[p45cal]

try changing your named ranges from whole columns to dynamic ones:
AmountDue =OFFSET(Sheet1!$N$1,,,COUNTA(Sheet1!$A:$A))
BillingLine =OFFSET(Sheet1!$F$1,,,COUNTA(Sheet1!$A:$A))
Month =OFFSET(Sheet1!$J$1,,,COUNTA(Sheet1!$A:$A))
Tier =OFFSET(Sheet1!$H$1,,,COUNTA(Sheet1!$A:$A))

At the moment it looks as if the whole 1 million+ rows are being processed.

[tkaplan]

I tried changing it to just rows 1 through 5000 and it still causes the same problem.....

[p45cal]

Well, when I looked at your sheet it was slow (didn't crash), changing to dynamic ranges speeded it up to the extent that it was hardly noticeable, trying:
AmountDue =Sheet1!$N$1:$N$5000
BillingLine =Sheet1!$F$1:$F$5000
Month =Sheet1!$J$1:$J$5000
Tier =Sheet1!$H$1:$H$5000
was equally fast.

Did you try with the dynamic ranges suggested?

[tkaplan]

the dynamic ranges works great now! thanks!!! for some reason when I did the rows 1 through 5000 it didn't help at first. but now that i changed it to dynamic, then changing it to 1 through 5000 works great now too. i guess it just wasn't liking me today.

thanks for your help!!

[Bob Phillips]

Why aren';t you using COUNTIFS and SUMIFS?

=COUNTIFS(BillingLine,$A2,Tier,B$1,Month,$A$1,AmountDue,"<>0")

=SUMIFS(AmountDue,BillingLine,$A2,Tier,C$1,Month,$A$1)/C2

and so on

[tkaplan]

what would be the advantage of using that over sumproduct?

[Bob Phillips]

Efficiency. It is probable you wouldn't get the freezing. SUMIFS are efficient functions, SUMPRODUCT is not.

[tkaplan]

I'm being stupid.....I need this to work in excel 2003 as well. if I remember correctly countifs is not available in 2003?

[mancubus]

you're correct.



i tried the following CSE formulas for B2. performans is not better than sumproduct (as may be expected)!

=COUNT(IF(BillingLine=$A2,IF(Tier=B$1,IF(Month=$A$1,IF(AmountDue<>0,AmountDue)))))

=SUM((BillingLine=$A2)*(Tier=B$1)*(Month=$A$1)*(AmountDue<>0))