Count Unique Cells, within a date range (dates stored in separatecolumn).

[Matt]

Hello All:

I am attempting to create a formula that will count the number of
unique cells (alpha-numeric) in an entire column (no set length, so
ex: "$B:$B"). This count will be based on a date range (Between 2
dates). The dates are stored in another column (Ex: "$A:$A"). The
following is an example of the cells.

Dates PO_Num
1/2/2010 A123456
1/3/2010 B453453
2/2/2010 A123456
3/4/2010 C2342F3K
4/3/2010 123456

I would like to know a formula that can count the number of different
PO's based on a given date range.

Please help me if you can. If there is no formula but there is VB
code, please post as well. Any help is welcome.

Thank you in advance,

Matt

 

[Hans Terkelsen]

Hello All:

I am attempting to create a formula that will count the number of
unique cells (alpha-numeric) in an entire column (no set length, so
ex: "$B:$B"). This count will be based on a date range (Between 2
dates). The dates are stored in another column (Ex: "$A:$A"). The
following is an example of the cells.

Dates PO_Num
1/2/2010 A123456
1/3/2010 B453453
2/2/2010 A123456
3/4/2010 C2342F3K
4/3/2010 123456

I would like to know a formula that can count the number of different
PO's based on a given date range.

Please help me if you can. If there is no formula but there is VB
code, please post as well. Any help is welcome.

Thank you in advance,

Matt

Hi Matt.

C1, D1 the limit dates

Try this formula trick:
=SUMPRODUCT(1/COUNTIF(B2:B6,B2:B6)*(C1<=A2:A6)*(D1>=A2:A6)*(""<B2:B6))

to count how many different text PO_Num's there is in B2:B6
Duplicates each count for 1/2, triplicates for 1/3 ...

Hans T.

 

[Matt]

Hi Matt.

C1, D1 the limit dates

Try this formula trick:
=SUMPRODUCT(1/COUNTIF(B2:B6,B2:B6)*(C1<=A2:A6)*(D1>=A2:A6)*(""<B2:B6))

to count how many different text PO_Num's there is in B2:B6
Duplicates each count for 1/2, triplicates for 1/3 ...

Hans T.- Hide quoted text -

- Show quoted text -

Hello:

Thank you for the reply. Your formula works great for a small amount
of data. I'm dealing with anywhere from 100 to 95000 rows. The formula
just takes too long to compile.

I think I'm going to have to seek VB code in order to find an
efficient way of getting what I am looking for.

Again, thank you for the reply, but I'm still looking.

-Matt

 

[Hans Terkelsen]

Hi Matt.

C1, D1 the limit dates

Try this formula trick:
=SUMPRODUCT(1/COUNTIF(B2:B6,B2:B6)*(C1<=A2:A6)*(D1>=A2:A6)*(""<B2:B6))

to count how many different text PO_Num's there is in B2:B6
Duplicates each count for 1/2, triplicates for 1/3 ...

Hans T.- Hide quoted text -

- Show quoted text -

Hello:

Thank you for the reply. Your formula works great for a small amount
of data. I'm dealing with anywhere from 100 to 95000 rows. The formula
just takes too long to compile.

I think I'm going to have to seek VB code in order to find an
efficient way of getting what I am looking for.

Again, thank you for the reply, but I'm still looking.

-Matt

Matt,

Actually scratch my first effort, it was not fully thought through.

And about using the COUNTIF over ~100,000 lines,
the calculation time goes as the sqare of the number of lines.
That is a lot.

A way around could be to get Laurent Longres free Add-In Morefunc from
http://xcell05.free.fr/

It has a lot of valuable functions, for example COUNTDIFF

I have tried with 50,000 lines of data the formula
=COUNTDIFF(INDEX(B:B,MATCH(C1-1,A:A)+1):INDEX(B:B,MATCH(D1,A:A)))
and it was not noticeably slow.

Dates in A:A sorted
PO_Nums in B:B
Limiting dates in C1, D1

This means that a tailormade function, if available, would also be fast.

Hans T.