Counting unique records based on date range

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:

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

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 <[email protected]>,
aspAddict <[email protected]>
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...

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...
>
>