retrieve unique items with 2 criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave Breitenbach - 29 Nov 2005 20:22 GMT

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

Reply to this Message

Vito - 29 Nov 2005 20:28 GMT

You don't need an argument for the "O array", try:

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

Reply to this Message

Dave Breitenbach - 29 Nov 2005 20:46 GMT

This gives me the #value error. Any other thoughts?
I thought the O qualifier was safeguarding against blank cells?

> You don't need an argument for the "O array", try:
>
> =SUMPRODUCT((($O$7:$O$2710)*($M$7:$M$2710="1986"))/COUNTIF($O$7:$O$2710,$O$7:$O$2710&""))

Reply to this Message

Bob Phillips - 29 Nov 2005 20:48 GMT

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

Signature

HTH

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

> 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.
[quoted text clipped - 4 lines]
>
> 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
[quoted text clipped - 4 lines]
>
> This seems to apply only the second criteria

Reply to this Message

Dave Breitenbach - 29 Nov 2005 21:31 GMT

Thanks Bob - this worked.

> Dave,
>
[quoted text clipped - 27 lines]
> >
> > This seems to apply only the second criteria

Reply to this Message

Vito - 29 Nov 2005 21:48 GMT

Thanks Bob for helping out.:)

Signature

Vito

Reply to this Message

Dave Breitenbach - 30 Nov 2005 15:06 GMT

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

> Dave,
>
[quoted text clipped - 27 lines]
> >
> > This seems to apply only the second criteria

Reply to this Message

Domenic - 30 Nov 2005 19:15 GMT

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!

> 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
[quoted text clipped - 41 lines]
> > >
> > > This seems to apply only the second criteria

Reply to this Message

Dave Breitenbach - 30 Nov 2005 21:22 GMT

You guys continue to impress. This is great.
I do have a couple of questions though.

If I understand this correctly, the match formula can have a lookup value as
an array instead of just a single value. If its lookup array and lookup value
are the same, then it is simply giving an array of the position of each item
in the total range used in the lookup value/lookup range.
It appears you've concatenated the 2 criteria columns for the purpose of
locating their commonalities in the array. This is intuitive looking back on
it but clever without knowing it. Was the "#" an arbitrary choice for a
connector? Why is it necessary to have any connector?

thanks to both Domenic and Bob for this one!

Dave

> Try...
>
[quoted text clipped - 50 lines]
> > > >
> > > > This seems to apply only the second criteria

Reply to this Message

Domenic - 30 Nov 2005 23:03 GMT

The "#" is used to deal with situations where you have...

1,11
11,1

Without "#"...

111
111

With "#"...

1#11
11#1

Note that you can use other characters as well for this purpose. For
example, you can also use "@".

Hope this helps!

> You guys continue to impress. This is great.
> I do have a couple of questions though.
[quoted text clipped - 11 lines]
>
> Dave

Reply to this Message

Dave Breitenbach - 30 Nov 2005 23:16 GMT

Great. Thanks again.

> The "#" is used to deal with situations where you have...
>
[quoted text clipped - 31 lines]
> >
> > Dave

Reply to this Message