passing dates in a conditional sum(if)

[Neophyte]

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),amount07))}

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

 

[Bob Phillips]

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]

Thanks for the response. The ranges of Customer07 are not the same as Date07
and Amount07(they are =). If I make all the named ranges the same number of
rows(10205 is the largest worksheet) and some are blank, will this mess up
sumproduct? Or do they have to be the same range?
I think I read that they had to be the same for sumproduct and that is why I
chose sum(if) as I never could get sumproduct to work.
What does the -- mean?
When I enter sumproduct in the cell I get a #value! error.

Thanks,
Lee Coleman