Sumproduct and speed of Calc.

[Lee]

I have Excel 2007 on a 1.8 core 2 duo with buss of 1066. The workbook
contains 3 sheets that are of 1) sales from 2004 to 2006, 2) customers, and
3) current year sales. The 2004 to 2006 sales has 286,390 rows with 14
columns, customers has 607 rows, and current year sales is at 42,252 rows
with 14 columns. I use the customer sheet to compare sales by customer by
month and year to date for the previos years 2004, 2005, and 2006 to this
year's sales. I know I ask a lot of the program and computer but it takes it
15 to 17 minutes to calculate a change. I set it to manual calc and my
question is there a way to speed this up? I use sumproduct with 4 variables
and a calculation. I am relatively new to this and found out most of the
info for sumproduct() on http://www.xldynamic.com/source/xld.SUMPRODUCT.html
.. If I seperated the prior years into individual sheets of 90,000 rows would
this speed up the calc?
Thanks to all for any help or suggestions.
Lee

 

[Bob Phillips]

You probably need to

- break the SP formulae up to stop every recalculation
- use VBA
- a bit of both.

Give a simple layout of the sheets and the formulae and let's take a more
detailed look.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JLatham]

Lee,
I'd keep working with Bob Phillips to see if you can't improve upon what you
have. I'm just popping in to kind of drop a hint that it things don't work
out in Excel, you probably already have a good data structure set up that
could be imported into Access, if you have it, and then the analysis could be
performed via queries - and probably in a lot less time. Just consider this:
prior to 2007, you would have had to do one of 2 things: split your prior
year's sales into multiple sheets (about 5 of them) or moved on to Access
already.

On the other hand, you already have formulas and such set up that give you
the numbers you need to see - it just takes a little time. It is reasonable
to expect you not to want to throw something away that isn't actually broken
- just needs a little WD-40 here and there. So looking into speeding up what
you have is a reasonable and responsible action.

 

[Peo Sjoblom]

Another thing to think of since the OP is using 2007, the new functions
SUMIFS and COUNTIFS might be able to replace the SP formulas in most cases
and that might speed up the workbook quite a bit

 

[Lee]

Prior Years and Current Year columns are the same:
A) Customer Number
B) Item Number
C) Invoice Number
D) Salesperson
E) Line Type
F) Date
G) Month - taken from =month(F1) formated as a number
H) Year - taken from =year(F1) formated as a number
I) QTY Sold
J) Price
K) Pot Code
L) Customer Code
M) Description
N) Line Item Total - taken from =I1*J1 formated as currency no symbol

Customer Sheet is
A1) Beginning Month
B1) I enter the beginning month as a number Jan=1 ect
C1) Ending Month
D1) I enter the ending month as a number.

A2) Customer Number
B2) Customer Code
C2) Customer Name
D2) Salesperson
.....
H2) Current Year 2007
I2) 2006
J2) 2005
K2) 2004

The formulae for H3 and below: Current Year
=SUMPRODUCT(1*(CurrYear!$A$2:$A$42252=Customers!A3)*(CurrYear!$G$2:$G$42252>=Customers!$B$1)*(CurrYear!$G$2:$G$42252<=Customers!$D$1)*(CurrYear!$M$2:$M$42252))

The formulae for the prior years is:
=SUMPRODUCT(1*(PriorYears!$A$2:$A$286390=Customers!$A3)*(PriorYears!$G$2:$G$286390>=Customers!$B$1)*(PriorYears!$G$2:$G$286390<=Customers!$D$1)*(PriorYears!$H$2:$H$286390=2006),(PriorYears!$N$2:$N$286390))

Sorry for being verbose but didn't want to leave anything out. Thanks for
any help and please remember I am relatively new to this.
Lee