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)*(CurrYe ar!$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)*(Prior Years!$H$2:$H$286390=2006),(PriorYears!$N$2:$N$286 390))

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

"Bob Phillips" wrote in message
...
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)

"Lee" wrote in message
...
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