my data set is really large (+400,000 rows)

Yeah, that could slow things down when you're counting uniques on that size
range!

I know that the data is ordered

Despite the length of this formula it's much better! It narrows down the
range to only those cells that are of the same ID.

Array entered**

=SUM(IF(FREQUENCY(IF(INDEX(A:A,MATCH(D2,A:A,0)):IN DEX(A:A,MATCH(D2,A:A,0)+COUNTIF(A:A,D2)-1)=D2,INDEX(B:B,MATCH(D2,A:A,0)):INDEX(B:B,MATCH(D 2,A:A,0)+COUNTIF(A:A,D2)-1)),INDEX(B:B,MATCH(D2,A:A,0)):INDEX(B:B,MATCH(D2, A:A,0)+COUNTIF(A:A,D2)-1)),1))

If you can download and install the free Morefunc add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/300...-10423159.html

Then you can use this slightly shorter array entered** version:

=SUM(IF(FREQUENCY(IF(INDEX(A:A,MATCH(D2,A:A,0)):IN DEX(A:A,MATCH(D2,A:A,0)+COUNTIF(A:A,D2)-1)=D2,SETV(INDEX(B:B,MATCH(D2,A:A,0)):INDEX(B:B,MA TCH(D2,A:A,0)+COUNTIF(A:A,D2)-1))),GETV()),1))** array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFTkey then hit ENTER.--BiffMicrosoft Excel MVP"Juan Marin" wrote in ... Thank you so much Ron and Biff, both solutions work pretty well. Nevertheless I'm encountering one more difficulty, my data set is really large (+400,000 rows) so pasting down any of these formulas is not practical because as I paste them down, they stay fixed to the first cell of the range. However, I know that the data is ordered, and that in any case, if I were able to look just 100 rows forward and 100 rows ahead, that would be enough. I've been trying to fix both formulas with ADDRESS() in such a way that I could vary the initial and ending rows in such way, but I haven't been able to do so effectively. Any thoughts? Thank you again. JM