View Full Version : Solved: sumproduct freezing the file
tkaplan
03-11-2011, 07:24 AM
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
03-11-2011, 08:53 AM
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
03-11-2011, 10:44 AM
I tried changing it to just rows 1 through 5000 and it still causes the same problem.....
p45cal
03-11-2011, 10:57 AM
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
03-11-2011, 12:34 PM
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
03-11-2011, 03:57 PM
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
03-14-2011, 06:11 AM
what would be the advantage of using that over sumproduct?
Bob Phillips
03-14-2011, 06:28 AM
Efficiency. It is probable you wouldn't get the freezing. SUMIFS are efficient functions, SUMPRODUCT is not.
tkaplan
03-14-2011, 06:34 AM
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
03-15-2011, 01:12 AM
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))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.