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
------------------------------------------------------------------------
aspAddict's Profile: http://www.excelforum.com/member.php...o&userid=28337
View this thread: http://www.excelforum.com/showthread...hreadid=479171

Counting unique records based on date range
 

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

Counting unique records based on date range
 

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

Counting unique records based on date range
 

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