Home |
Search |
Today's Posts |
#1
|
|||
|
|||
HELP!!! On SumProduct
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 |
#2
|
|||
|
|||
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) -- HTH Bob Phillips "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:K1 0000) 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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |