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

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

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" wrote:

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

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

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.

"ND Pard" wrote:

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" wrote:

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

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

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


"adimar" wrote:


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.

"ND Pard" wrote:

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" wrote:

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

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

On May 28, 5:25 pm, adimar wrote:
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.

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.

"Spiky" wrote:

On May 28, 5:25 pm, adimar wrote:
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

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