HELP!!! On SumProduct

[Wally]

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

 

[Bob Phillips]

Haven't tested it, but you cannot test dates that way. Try

=SUMPRODUCT(--(TEXT(TICKETS!C2:C10000,"mmmyyyy")="Jan2005"),--(TICKETS!E2:E1
0000=TEST!A12),--(TICKETS!G2:G10000=TEST!C12),TICKETS!K2:K10000)

 

[Bob Umlas]

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