Try...

=SMALL(IF((M7:M100=1986)*(MATCH(M7:M100&"#"&O7:O10 0,M7:M100&"#"&O7:O100,0
)=ROW(O7:O100)-ROW(O7)+1),O7:O100),2)

....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

Hope this helps!

In article ,
"Dave Breitenbach" wrote:

Alright. Question answered but I've got another one. Of the unique values
in column O, I've been trying to apply the small function to the formula
below to give me the kth smallest value of the unique O column values. I've
tried a few positions including the following but have not had any luck:

=SMALL(--(FREQUENCY(IF(TRIM('prepay detail'!M7:M2710)=$B32,MATCH('prepay
detail'!O7:O2710,'prepay detail'!O7:O2710,0)),ROW(INDIRECT(
"1:"&ROWS('prepay detail'!O7:O2710))))0),1)

any thoughts?

tia,
Dave

"Bob Phillips" wrote:

Dave,

You could use

=SUM(--(FREQUENCY(IF(M7:M2710=1986,MATCH(O7:O2710,O7:O271 0,0)),ROW(INDIRECT(
"1:"&ROWS(O7:O2710))))0))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Breitenbach" wrote in
message ...
I've been using the following formula from the xldynamic website detailing
sumproduct usage as a base for retrieving uniqe instances in a data
series.
I've been trying to modify the formula to allow me to utilize one
additional
criteria.
Original formula:
=SUMPRODUCT((A1:A20<"")/COUNTIF(A1:A20,A1:A20&""))

Here is my formula:

=SUMPRODUCT((($O$7:$O$2710<"")*($M$7:$M$2710="198 6"))/COUNTIF($O$7:$O$2710,
$O$7:$O$2710&""))

I'm trying to say how many unique instances are there for column O, where
column m = "1986?" Not sure what I'm doing wrong but help would be
appreciated.

tia,
Dave


This seems to apply only the second criteria