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