Counting unique records based on date range

[aspAddict]

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

 

[vezerid]

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

 

[Domenic]

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!

 

[Aladin Akyurek]

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.