Count the number of unique records

I have this table of data:

refid Date
95528 10/22/2004
95528 10/22/2004
95312 10/22/2004
95312 10/22/2004
95261 10/22/2004
95261 10/22/2004
95051 10/23/2004
95051 10/23/2004
95051 10/23/2004
94999 10/23/2004
94999 10/23/2004
94806 10/24/2004
94806 10/24/2004
94737 10/24/2004
94737 10/24/2004
94678 10/24/2004

I need to do a count only counts a REFID once and only between certian
dates. So, sort of count the number of REFID's that fall on 10/23/2004.
Make sence?

Also, it needs to be done in one formula. Obviously this requires an
array formula. I have a formula that counts the number of entires
between certian dates, but I can't figure out how to count a REFID only
once.

Cheers!

=SUMPRODUCT(--($B$2:$B$17=D2),($A$2:$A$17<>"")/COUNTIF($A$2:$A$17,$A$2:$A$17&""))

where D2 houses the date of interest.

[email protected] wrote:
> I have this table of data:
>
> refid Date
> 95528 10/22/2004
> 95528 10/22/2004
> 95312 10/22/2004
> 95312 10/22/2004
> 95261 10/22/2004
> 95261 10/22/2004
> 95051 10/23/2004
> 95051 10/23/2004
> 95051 10/23/2004
> 94999 10/23/2004
> 94999 10/23/2004
> 94806 10/24/2004
> 94806 10/24/2004
> 94737 10/24/2004
> 94737 10/24/2004
> 94678 10/24/2004
>
> I need to do a count only counts a REFID once and only between certian
> dates. So, sort of count the number of REFID's that fall on 10/23/2004.
> Make sence?
>
> Also, it needs to be done in one formula. Obviously this requires an
> array formula. I have a formula that counts the number of entires
> between certian dates, but I can't figure out how to count a REFID only
> once.
>
> Cheers!
>

If you want to count only once the unique, this comes close but I get a 0.
Maybe someone could help this poor formula out to give 1 as an answer?
Array entered:
=SUM(IF(FREQUENCY(IF((A2:A17=95528)*(B2:B17="10/22/2004"),MATCH(A2:A17,A2:A17,0),""),IF((A2:A17=95528)*(B2:B17="10/22/2004"),MATCH(A2:A17,A2:A17,0),""))>0,1))



"[email protected]" wrote:

> I have this table of data:
>
> refid Date
> 95528 10/22/2004
> 95528 10/22/2004
> 95312 10/22/2004
> 95312 10/22/2004
> 95261 10/22/2004
> 95261 10/22/2004
> 95051 10/23/2004
> 95051 10/23/2004
> 95051 10/23/2004
> 94999 10/23/2004
> 94999 10/23/2004
> 94806 10/24/2004
> 94806 10/24/2004
> 94737 10/24/2004
> 94737 10/24/2004
> 94678 10/24/2004
>
> I need to do a count only counts a REFID once and only between certian
> dates. So, sort of count the number of REFID's that fall on 10/23/2004.
> Make sence?
>
> Also, it needs to be done in one formula. Obviously this requires an
> array formula. I have a formula that counts the number of entires
> between certian dates, but I can't figure out how to count a REFID only
> once.
>
> Cheers!
>
>

Array entered... this gives 1 as an answer (is this helpful ?)
=SUM(IF(FREQUENCY(IF((A2:A17=D1)*(B2:B17=C1),MATCH(B2:B17,B2:B17,0),""),IF((A2:A17=D1)*(B2:B17=C1),MATCH(B2:B17,B2:B17,0),""))>0,1))
Where C1 is your date and D1 is the redfid number.

"GerryK" wrote:

> If you want to count only once the unique, this comes close but I get a 0.
> Maybe someone could help this poor formula out to give 1 as an answer?
> Array entered:
> =SUM(IF(FREQUENCY(IF((A2:A17=95528)*(B2:B17="10/22/2004"),MATCH(A2:A17,A2:A17,0),""),IF((A2:A17=95528)*(B2:B17="10/22/2004"),MATCH(A2:A17,A2:A17,0),""))>0,1))
>
>
>
> "[email protected]" wrote:
>
> > I have this table of data:
> >
> > refid Date
> > 95528 10/22/2004
> > 95528 10/22/2004
> > 95312 10/22/2004
> > 95312 10/22/2004
> > 95261 10/22/2004
> > 95261 10/22/2004
> > 95051 10/23/2004
> > 95051 10/23/2004
> > 95051 10/23/2004
> > 94999 10/23/2004
> > 94999 10/23/2004
> > 94806 10/24/2004
> > 94806 10/24/2004
> > 94737 10/24/2004
> > 94737 10/24/2004
> > 94678 10/24/2004
> >
> > I need to do a count only counts a REFID once and only between certian
> > dates. So, sort of count the number of REFID's that fall on 10/23/2004.
> > Make sence?
> >
> > Also, it needs to be done in one formula. Obviously this requires an
> > array formula. I have a formula that counts the number of entires
> > between certian dates, but I can't figure out how to count a REFID only
> > once.
> >
> > Cheers!
> >
> >

Looks like its gettting close. I'll have to work on that formula a bit.
Thanks for the reccomendation.

This one is VERY cpu intensive.

Wait, I lied.
=SUMPRODUCT(--(MONTH($D$3:$D$10000)=$F5),($C$3:$C$10000<>"")/COUNTIF($C$3:$C$10000,$C$3:$C$10000&""))
(this is entered as an array formula)
Also, where $F5 is the month number in question (IE 12 for December)

This works, its just VERY cpu intensive.

You can probably obtain a better performance score with:

=COUNTDIFF(IF($D$3:$D$10000-DAY($D$3:$D$10000)+1=$F5,$C$3:$C$10000,""),FALSE,"")

which must be confirmed with control+shift+enter, not just with enter.

Note 1. You need to download and install the morefunc.xll add-in.

Note 2. F5 must house the first day date of a month/year of interest.
For example: 1-Oct-2004. Take notice of 1 as day number.

Note 3. If C:D is sorted in ascending order on column D, we can do even
better qua performance.

[email protected] wrote:
> Wait, I lied.
> =SUMPRODUCT(--(MONTH($D$3:$D$10000)=$F5),($C$3:$C$10000<>"")/COUNTIF($C$3:$C$10000,$C$3:$C$10000&""))
> (this is entered as an array formula)
> Also, where $F5 is the month number in question (IE 12 for December)
>
> This works, its just VERY cpu intensive.
>