Maybe this

=SUMPRODUCT(--(Custnumber07=A3),--(date07<=DATE(YEAR(date07),MONTH($J$1),DAY($J$1))) ,amount07)

--
HTH

Bob

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

"Neophyte" wrote in message
...
I have a workbook with 5 sheets.
Sheet1 is Customers
Sheet2 is YTD Sales 07
Sheet3 is Total Sales 06
Sheet4 is Total Sales 05
Sheet5 is Total Sales 04

Customer sheet has this basic formula in the columns for each year's sales
based on the customer's number and the variable of the month that the
sales was done. {=SUM(IF((Custnumber07=A3)*(Month07<=$J$1),amount0 7))}

The problem is that the accounting program exports the invoices with the
MMDDYY for each invoice. The $j$1 is the number of the month I limit it up
to. I have a column in each sales sheet with the month() function to pull
the month out. This works ok but it always pulls the entire months from
the completed years and present year's sales always look behind. Completed
months give good info but to run the report in mid month is deceiving.
I want to be able to put a variable date range in the sum(if) array and
have run into a brickwall. I have tried using the Date(,month(),day())with
no luck. I think I have to use it because the years are different.
I think that the formula should read something like "if the customer
number is the same and the invoice date is <= $j$1 then sum invoice
amount". J1 is the date as 3/15/07 and date07 is the range where it takes
the invoice date and strips it of the year using date(,month(e2),day(e2))
so that the date reads 3/15 not 3/15/07 or 3/15/06...
{=SUM(IF((Custnumber07=A5)*(date07<=DATE(,MONTH($J $1),DAY($J$1))),amount07))}
and get #N/A
Any help or guidance is greatly appreciated. I have searched on Chip
Pearson's, Microsoft's and others websites with no luck.
Thanks,
Lee Coleman