unique records in specified range

[adimar]

I have a very large table of non-unique items shipped; the table contains
“item id†and “ship date†columns.

I need to find the number of unique “item id-s†shipped in a given
timeframe, ex. 01/01/08 - 03/31/08

I’ve tried using the commonly quoted solution:
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Somehow, I cannot get the additional check for timeframe to work in the
above formula, something like:
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")*(B1:B10 >= C1)*( B1:B10
<= C2))

I would appreciate suggestions for array formula solutions.

Thank you.

 

[ND Pard]

Try an array function like:

=Count(If(A1:A10="item
id-s",If(B1:B10>=Date(2008,1,1),If(B1:B10<Date(2008,4,1),A1:A1))))

Finally, because it is an Array function, while holding down the Cntrl and
Shft Key, press Enter. This will enclose the function in braces { }.

Good Luck.

 

[adimar]

I must have not been clear about the "item id-s". Here is a data sample:

shipped item id
==============
05/16/02 b02100535
06/11/02 b02100525
06/11/02 b02100534
06/13/02 b02100524
06/13/02 b02100533
07/03/02 b02100528
07/16/02 b02100531
07/16/02 b02100532
10/04/02 b02100527
11/12/02 b02100534
05/22/03 b02100530
10/10/03 b02100534
06/14/04 b02100525
01/26/05 b02100532
02/11/05 b02100534
08/16/05 b02100524
08/16/05 b02100533
10/12/05 b02100533
10/17/05 b02100524
12/02/05 b02100528
06/29/06 b02100524

I'm looking for the number of unique item id-s that shipped in 2005.

Thank you.

 

[Teethless mama]

Try this:
"Shipped" & "item_id" are defined name ranges

=SUM(N(FREQUENCY(IF(YEAR(Shipped)=2005,MATCH(item_id,item_id,0)),MATCH(item_id,item_id,0))>0))

ctrl+shift+enter, not just enter

 

[Spiky]

I have a very large table of non-unique items shipped; the table contains
“item id” and “ship date” columns.

I need to find the number of unique “item id-s” shipped in a given
timeframe, ex. 01/01/08 - 03/31/08

I’ve tried using the commonly quoted solution:
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Somehow, I cannot get the additional check for timeframe to work in the
above formula, something like:
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")*(B1:B10 >= C1)*( B1:B10
<= C2))

I would appreciate suggestions for array formula solutions.

Thank you.

Google "morefunc". They have a free UDF that supposedly does exactly
this.

 

[adimar]

morefunc/COUNTDIFF counts unique occurences in an array.

I'm not clear how to get the source code to update it to add the additional
check for the date range.

Thank you.