Excel is interpreting 1/1/5 in your formula as 1 divided by 1 divided by 5,
not as a date. Similarly with 2/1/5. Change these to DATEVALUE("1/1/5") and
DATEVALUE("2/1/5"). If you STILL are getting #VALUE, I would bet that
there's already a #VALUE somethere in the ranges you're looking at
(TICKETS!C2:K10000 somewhere likely contains #VALUE) -- clear these up.
Bob Umlas
Excel MVP
"Wally" wrote in message
...
On my test sheet I have a Customer name (TEST!A12) and a Product ID
(TEST!C12).
I want to find out how much of the product that customer pulled per
month from my data sheet. (TICKETS!)
Below is the formula I used, to try and pull the information for the
month of Jan. But it returns #VALUE
=SUMPRODUCT(--(TICKETS!C2:C10000=1/1/5),--(TICKETS!C2:C10000<2/1/5),-
-(TICKETS!E2:E10000=TEST!A12),--(TICKETS!G2:G10000=TEST!C12),TICKETS!K2:K10000)
Thanks for any help you can give me.
By the way, if I haven't said it.. Thanks.everyone has been great in
putting up with all my questions and requests for help :)
Wally
|