Count the number of unique records

[joshczinger]

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!

 

[Aladin Akyurek]

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

 

[Guest]

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

 

[Guest]

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.

 

[joshczinger]

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

 

[joshczinger]

This one is VERY cpu intensive.

 

[joshczinger]

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.

 

[Aladin Akyurek]

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 is sorted in ascending order on column D, we can do even
better qua performance.