Re: retrieve unique items with 2 criteria

Try...

=SMALL(IF((M7:M100=1986)*(MATCH(M7:M100&"#"&O7:O100,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 <07FDC55C-4184-425B-A900-1677FDAD439A@xxxxxxxxxxxxx>,
"Dave Breitenbach" <DaveBreitenbach@xxxxxxxxxxxxxxxxxxxxxxxxx> 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:O2710,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" <DaveBreitenbach@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> > message news:04F1B3CE-7F39-4182-9E50-6BA9CCA23CBA@xxxxxxxxxxxxxxxx
> > > 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="1986"))/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
> >
> >
> >
.

Relevant Pages