Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]() This is fairly hard to describe, so bear with me: I have 2 sheets in a workbook - Sheet1 has 3113 rows of order data as follows: Sheet1 A B 1 ORDER_DATE CUST_ORDER_ID 2 09/06/05 71000-05 3 09/06/05 71001-05 4 09/06/05 71001-05 5 09/06/05 71001-05 6 09/06/05 71002-05 7 09/06/05 71004-05 8 09/06/05 71004-05 9 09/06/05 71004-05 10 09/06/05 71004-05 ...and so on... As you can see, there are multiple orders with the same order number AND on the same date. (On 9/6/2005, there were three items that went out on order number 71001-05, 1 item on order 71002, etc.) What I am trying to do on Sheet2 is count the unique order numbers by date, something like this: Sheet2 A B 1 ORDER DATE TOTAL ORDERS 2 9/6/2005 4 3 9/7/2005 14 4 9/8/2005 8 5 9/9/2005 12 And so on - I have manually entered the dates on Sheet2 and would like the query to be able to read from the date cell, check Sheet1 for all orders matching that date, then count the unique order numbers... Any suggestions? I've Googled just about everything I can, but I either end up with a formula that counts all 3113 rows, or a #DIV/0 error....*sigh* TIA... -- aspAddict ------------------------------------------------------------------------ aspAddict's Profile: http://www.excelforum.com/member.php...o&userid=28337 View this thread: http://www.excelforum.com/showthread...hreadid=479171 |
#2
![]() |
|||
|
|||
![]()
aspAddict:
The following formula will work IF your original data in Sheet1 is sorted by date then cust_id. In Sheet2!B2 supply the following ARRAY formula: =SUM((Sheet1!$A$2:$A$3113=A2)*(Sheet1!$B$2:$B$3113 <$B$3:$B$3114)) to be entered with Shift+Ctrl+Enter HTH Kostis Vezerides |
#3
![]() |
|||
|
|||
![]()
Assuming A1:B10 contains your data, and D2 contains the date of
interest, try... =SUMPRODUCT(--($A$2:$A$10=$D2),--($B$2:$B$10<""),--(MATCH($A$2:$A$10&"#" &$B$2:$B$10,$A$2:$A$10&"#"&$B$2:$B$10,0)=ROW($B$2: $B$10)-ROW($B$2)+1)) or =SUM(IF(FREQUENCY(IF(($A$2:$A$10=$D2)*($B$2:$B$10< ""),MATCH($B$2:$B$10,$ B$2:$B$10,0)),ROW($B$2:$B$10)-ROW($B$2)+1)0,1)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the ranges accordingly. Hope this helps! In article , aspAddict wrote: This is fairly hard to describe, so bear with me: I have 2 sheets in a workbook - Sheet1 has 3113 rows of order data as follows: Sheet1 A B 1 ORDER_DATE CUST_ORDER_ID 2 09/06/05 71000-05 3 09/06/05 71001-05 4 09/06/05 71001-05 5 09/06/05 71001-05 6 09/06/05 71002-05 7 09/06/05 71004-05 8 09/06/05 71004-05 9 09/06/05 71004-05 10 09/06/05 71004-05 ..and so on... As you can see, there are multiple orders with the same order number AND on the same date. (On 9/6/2005, there were three items that went out on order number 71001-05, 1 item on order 71002, etc.) What I am trying to do on Sheet2 is count the unique order numbers by date, something like this: Sheet2 A B 1 ORDER DATE TOTAL ORDERS 2 9/6/2005 4 3 9/7/2005 14 4 9/8/2005 8 5 9/9/2005 12 And so on - I have manually entered the dates on Sheet2 and would like the query to be able to read from the date cell, check Sheet1 for all orders matching that date, then count the unique order numbers... Any suggestions? I've Googled just about everything I can, but I either end up with a formula that counts all 3113 rows, or a #DIV/0 error....*sigh* TIA... |
#4
![]() |
|||
|
|||
![]()
If you download and install the latest version (3.9 by now) of Longre's
free morefunc.xll add-in: With column D from D2 on housing order dates of interest... =COUNTDIFF(IF((Sheet1!$A$2:$A$10=D2)*(Sheet1!$B$2: $B$10<""),Sheet1!$B$2:$B$10,0),FALSE,0) which must be confirmed with control+shift+enter, not just with enter, then copied down. aspAddict wrote: This is fairly hard to describe, so bear with me: I have 2 sheets in a workbook - Sheet1 has 3113 rows of order data as follows: Sheet1 A B 1 ORDER_DATE CUST_ORDER_ID 2 09/06/05 71000-05 3 09/06/05 71001-05 4 09/06/05 71001-05 5 09/06/05 71001-05 6 09/06/05 71002-05 7 09/06/05 71004-05 8 09/06/05 71004-05 9 09/06/05 71004-05 10 09/06/05 71004-05 ..and so on... As you can see, there are multiple orders with the same order number AND on the same date. (On 9/6/2005, there were three items that went out on order number 71001-05, 1 item on order 71002, etc.) What I am trying to do on Sheet2 is count the unique order numbers by date, something like this: Sheet2 A B 1 ORDER DATE TOTAL ORDERS 2 9/6/2005 4 3 9/7/2005 14 4 9/8/2005 8 5 9/9/2005 12 And so on - I have manually entered the dates on Sheet2 and would like the query to be able to read from the date cell, check Sheet1 for all orders matching that date, then count the unique order numbers... Any suggestions? I've Googled just about everything I can, but I either end up with a formula that counts all 3113 rows, or a #DIV/0 error....*sigh* TIA... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Date range criteria and Pivot tables (again!) | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel | |||
Show a date based on today | Excel Worksheet Functions | |||
Need to find Min value based on date range entered | Excel Worksheet Functions | |||
Need to find Min value based on date range entered | Excel Worksheet Functions |