Return Top values with changing criteria
 

I am trying to return the top N values from a list with corresponding
names. The criteria for ranking changes.
ColA:C contain my data (thousands of rows). ColD:E contain my
criteria (note below I want to return top 3 values). ColF sorts in
Top3 order.

ColA ColB ColC
ColD ColE ColF

Movie Friday $25
Movie Friday $26
Movie Friday $26
Movie Friday $25
Movie Friday $18
Movie Friday $18
TV Tues $12
Movie Tues $21
TV Tues $19
Movie Tues $19
TV Tues $21
Movie Tues $12

I would like to use a formla that can rank the data when the two
criteria in ColD & E are met. So when Movie and Friday show up in the
list of thousands, just return the top 3 values. Then when Movie and
Tues are met, return the top 3 values.

I know this can be done, but I am having trouble fixing my formulas
when the two criteria change.

Thanks in advance for your help.

Return Top values with changing criteria
 

Would this work out for you?
Duplicates are ranked as individual values:

ColA criteria in D1,
ColB criteria in E1,

Enter formula in F1 and copy down as many rows as the rankings you wish to
return.

=SUMPRODUCT(LARGE((A$2:A$13=D$1)*(B$2:B$13=E$1)*C$ 2:C$13,ROWS($1:1)))


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


wrote in message
...
I am trying to return the top N values from a list with corresponding
names. The criteria for ranking changes.
ColA:C contain my data (thousands of rows). ColD:E contain my
criteria (note below I want to return top 3 values). ColF sorts in
Top3 order.

ColA ColB ColC
ColD ColE ColF

Movie Friday $25
Movie Friday $26
Movie Friday $26
Movie Friday $25
Movie Friday $18
Movie Friday $18
TV Tues $12
Movie Tues $21
TV Tues $19
Movie Tues $19
TV Tues $21
Movie Tues $12

I would like to use a formla that can rank the data when the two
criteria in ColD & E are met. So when Movie and Friday show up in the
list of thousands, just return the top 3 values. Then when Movie and
Tues are met, return the top 3 values.

I know this can be done, but I am having trouble fixing my formulas
when the two criteria change.

Thanks in advance for your help.

Return Top values with changing criteria
 

Hi,

You can try this array formula (Ctrl+Shift+Enter) in cell E18. Copy this
formual down

=LARGE(($C$3:$C$14=C$18)*($D$3:$D$14=D$18)*($E$3:$ E$14),ROWS(C$18:C18))

I have assumed that the data is in range C3:E14. Movie is in cell C18 and
Friday is in cell D18

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I am trying to return the top N values from a list with corresponding
names. The criteria for ranking changes.
ColA:C contain my data (thousands of rows). ColD:E contain my
criteria (note below I want to return top 3 values). ColF sorts in
Top3 order.

ColA ColB ColC
ColD ColE ColF

Movie Friday $25
Movie Friday $26
Movie Friday $26
Movie Friday $25
Movie Friday $18
Movie Friday $18
TV Tues $12
Movie Tues $21
TV Tues $19
Movie Tues $19
TV Tues $21
Movie Tues $12

I would like to use a formla that can rank the data when the two
criteria in ColD & E are met. So when Movie and Friday show up in the
list of thousands, just return the top 3 values. Then when Movie and
Tues are met, return the top 3 values.

I know this can be done, but I am having trouble fixing my formulas
when the two criteria change.

Thanks in advance for your help.

Return Top values with changing criteria
 

On May 15, 5:46*pm, "RagDyer" wrote:
Would this work out for you?
Duplicates are ranked as individual values:

ColA criteria in D1,
ColB criteria in E1,

Enter formula in F1 and copy down as many rows as the rankings you wish to
return.

=SUMPRODUCT(LARGE((A$2:A$13=D$1)*(B$2:B$13=E$1)*C$ 2:C$13,ROWS($1:1)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message

...



I am trying to return the top N values from a list with corresponding
names. *The criteria for ranking changes.
ColA:C contain my data (thousands of rows). *ColD:E contain my
criteria (note below I want to return top 3 values). *ColF sorts in
Top3 order.

ColA * * * * * * *ColB * * * * * * *ColC
ColD * * * * * *ColE * * * * * * ColF

Movie * * * * * *Friday * * * * * * $25
Movie * * * * * Friday * * * * * $26
Movie * * * * * *Friday * * * * * * $26
Movie * * * * * Friday * * * * * $25
Movie * * * * * *Friday * * * * * * $18
Movie * * * * * Friday * * * * * $18
TV * * * * * * * *Tues * * * * * * * $12
Movie * * * * * Tues * * * * * * $21
TV * * * * * * * *Tues * * * * * * * $19
Movie * * * * * Tues * * * * * * $19
TV * * * * * * * *Tues * * * * * * * $21
Movie * * * * * Tues * * * * * * $12

I would like to use a formla that can rank the data when the two
criteria in ColD & E are met. *So when Movie and Friday show up in the
list of thousands, just return the top 3 values. *Then when Movie and
Tues are met, return the top 3 values.

I know this can be done, but I am having trouble fixing my formulas
when the two criteria change.

Thanks in advance for your help.- Hide quoted text -

- Show quoted text -

I need to allow for changing criteria in the same column. So in two
cells in the same two columns, I would have Movie and Wednesday, then
in the row beneath this one, I might have TV and Tuesday. The formula
above doesn't see that my criteria changed since it is using Absolute
references.

Let me know if there is a solution.

Thanks.

Conor

Return Top values with changing criteria
 

So, do you want the top 3 values to be listed side by side, across 3
columns?

Enter this formula in F1:

=SUMPRODUCT(LARGE(($A$2:$A$13=$D1)*($B$2:$B$13=$E1 )*$C$2:$C$13,COLUMNS($A:A)))

Then copy across to H1.

Then select F1 to H1. and copy that 3 cell selection down as needed.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message
...
On May 15, 5:46 pm, "RagDyer" wrote:
Would this work out for you?
Duplicates are ranked as individual values:

ColA criteria in D1,
ColB criteria in E1,

Enter formula in F1 and copy down as many rows as the rankings you wish to
return.

=SUMPRODUCT(LARGE((A$2:A$13=D$1)*(B$2:B$13=E$1)*C$ 2:C$13,ROWS($1:1)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message

...



I am trying to return the top N values from a list with corresponding
names. The criteria for ranking changes.
ColA:C contain my data (thousands of rows). ColD:E contain my
criteria (note below I want to return top 3 values). ColF sorts in
Top3 order.

ColA ColB ColC
ColD ColE ColF

Movie Friday $25
Movie Friday $26
Movie Friday $26
Movie Friday $25
Movie Friday $18
Movie Friday $18
TV Tues $12
Movie Tues $21
TV Tues $19
Movie Tues $19
TV Tues $21
Movie Tues $12

I would like to use a formla that can rank the data when the two
criteria in ColD & E are met. So when Movie and Friday show up in the
list of thousands, just return the top 3 values. Then when Movie and
Tues are met, return the top 3 values.

I know this can be done, but I am having trouble fixing my formulas
when the two criteria change.

Thanks in advance for your help.- Hide quoted text -

- Show quoted text -

I need to allow for changing criteria in the same column. So in two
cells in the same two columns, I would have Movie and Wednesday, then
in the row beneath this one, I might have TV and Tuesday. The formula
above doesn't see that my criteria changed since it is using Absolute
references.

Let me know if there is a solution.

Thanks.

Conor

Return Top values with changing criteria
 

On May 18, 7:29*pm, "RagDyer" wrote:
So, do you want the top 3 values to be listed side by side, across 3
columns?

Enter this formula in F1:

=SUMPRODUCT(LARGE(($A$2:$A$13=$D1)*($B$2:$B$13=$E1 )*$C$2:$C$13,COLUMNS($A:A*)))

Then copy across to H1.

Then select F1 to H1. and copy that 3 cell selection down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message

...
On May 15, 5:46 pm, "RagDyer" wrote:





Would this work out for you?
Duplicates are ranked as individual values:

ColA criteria in D1,
ColB criteria in E1,

Enter formula in F1 and copy down as many rows as the rankings you wish to
return.

=SUMPRODUCT(LARGE((A$2:A$13=D$1)*(B$2:B$13=E$1)*C$ 2:C$13,ROWS($1:1)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message

....

I am trying to return the top N values from a list with corresponding
names. The criteria for ranking changes.
ColA:C contain my data (thousands of rows). ColD:E contain my
criteria (note below I want to return top 3 values). ColF sorts in
Top3 order.

ColA ColB ColC
ColD ColE ColF

Movie Friday $25
Movie Friday $26
Movie Friday $26
Movie Friday $25
Movie Friday $18
Movie Friday $18
TV Tues $12
Movie Tues $21
TV Tues $19
Movie Tues $19
TV Tues $21
Movie Tues $12

I would like to use a formla that can rank the data when the two
criteria in ColD & E are met. So when Movie and Friday show up in the
list of thousands, just return the top 3 values. Then when Movie and
Tues are met, return the top 3 values.

I know this can be done, but I am having trouble fixing my formulas
when the two criteria change.

Thanks in advance for your help.- Hide quoted text -

- Show quoted text -

I need to allow for changing criteria in the same column. *So in two
cells in the same two columns, I would have Movie and Wednesday, then
in the row beneath this one, I might have TV and Tuesday. *The formula
above doesn't see that my criteria changed since it is using Absolute
references.

Let me know if there is a solution.

Thanks.

Conor- Hide quoted text -

- Show quoted text -

I would prefer the top 3 to show up as I drag down rows. The criteria
to the left of the column will be changed from TV to Movie, and from
Tues to Wednesday, etc. So in 3 rows, I would have the same criteria
to return the top 3 for that match. Then in the next 3 rows (right
below), I would have different criteria (all the same, but different
from the first 3) to return the top 3 for that.

Does that make sense? If not, I can work to post a sample
spreadsheet.

Thanks.

Conor

Return Top values with changing criteria
 

With this formula you *must* have 3 rows with the *same criteria* for it to
work correctly:

=SUMPRODUCT(LARGE((A$2:A$13=D2)*(B$2:B$13=E2)*C$2: C$13,MOD(ROWS($1:1)-1,3)+1))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
wrote in message
...
On May 18, 7:29 pm, "RagDyer" wrote:
So, do you want the top 3 values to be listed side by side, across 3
columns?

Enter this formula in F1:

=SUMPRODUCT(LARGE(($A$2:$A$13=$D1)*($B$2:$B$13=$E1 )*$C$2:$C$13,COLUMNS($A:A*)))

Then copy across to H1.

Then select F1 to H1. and copy that 3 cell selection down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message

...
On May 15, 5:46 pm, "RagDyer" wrote:





Would this work out for you?
Duplicates are ranked as individual values:

ColA criteria in D1,
ColB criteria in E1,

Enter formula in F1 and copy down as many rows as the rankings you wish
to
return.

=SUMPRODUCT(LARGE((A$2:A$13=D$1)*(B$2:B$13=E$1)*C$ 2:C$13,ROWS($1:1)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------

wrote in message

...

I am trying to return the top N values from a list with corresponding
names. The criteria for ranking changes.
ColA:C contain my data (thousands of rows). ColD:E contain my
criteria (note below I want to return top 3 values). ColF sorts in
Top3 order.

ColA ColB ColC
ColD ColE ColF

Movie Friday $25
Movie Friday $26
Movie Friday $26
Movie Friday $25
Movie Friday $18
Movie Friday $18
TV Tues $12
Movie Tues $21
TV Tues $19
Movie Tues $19
TV Tues $21
Movie Tues $12

I would like to use a formla that can rank the data when the two
criteria in ColD & E are met. So when Movie and Friday show up in the
list of thousands, just return the top 3 values. Then when Movie and
Tues are met, return the top 3 values.

I know this can be done, but I am having trouble fixing my formulas
when the two criteria change.

Thanks in advance for your help.- Hide quoted text -

- Show quoted text -

I need to allow for changing criteria in the same column. So in two
cells in the same two columns, I would have Movie and Wednesday, then
in the row beneath this one, I might have TV and Tuesday. The formula
above doesn't see that my criteria changed since it is using Absolute
references.

Let me know if there is a solution.

Thanks.

Conor- Hide quoted text -

- Show quoted text -

I would prefer the top 3 to show up as I drag down rows. The criteria
to the left of the column will be changed from TV to Movie, and from
Tues to Wednesday, etc. So in 3 rows, I would have the same criteria
to return the top 3 for that match. Then in the next 3 rows (right
below), I would have different criteria (all the same, but different
from the first 3) to return the top 3 for that.

Does that make sense? If not, I can work to post a sample
spreadsheet.

Thanks.

Conor

Return Top values with changing criteria
 

Hi,

Try this.

Assume your data is in range C3:E14. In cell C18, enter Movie and in cell
D18 enter Tuesday. In cell G18, enter =IF(C18="",G17,C18) and copy down a
couple of rows. In cell C18, use this array formula (Ctrl+Shift+Enter)

=LARGE(($C$3:$C$14=LOOKUP(REPT("z",99),C$18:C18))* ($D$3:$D$14=LOOKUP(REPT("z",99),D$18:D18))*($E$3:$ E$14),COUNTIF(G$18:G18,G18))

Now you can copy this formula down as many rows. Also, keep adding more
criteria such as TV and Tuesday

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I am trying to return the top N values from a list with corresponding
names. The criteria for ranking changes.
ColA:C contain my data (thousands of rows). ColD:E contain my
criteria (note below I want to return top 3 values). ColF sorts in
Top3 order.

ColA ColB ColC
ColD ColE ColF

Movie Friday $25
Movie Friday $26
Movie Friday $26
Movie Friday $25
Movie Friday $18
Movie Friday $18
TV Tues $12
Movie Tues $21
TV Tues $19
Movie Tues $19
TV Tues $21
Movie Tues $12

I would like to use a formla that can rank the data when the two
criteria in ColD & E are met. So when Movie and Friday show up in the
list of thousands, just return the top 3 values. Then when Movie and
Tues are met, return the top 3 values.

I know this can be done, but I am having trouble fixing my formulas
when the two criteria change.

Thanks in advance for your help.

Return Top values with changing criteria
 

On May 19, 7:59*pm, "Ashish Mathur" wrote:
Hi,

Try this.

Assume your data is in range C3:E14. *In cell C18, enter Movie and in cell
D18 enter Tuesday. *In cell G18, enter =IF(C18="",G17,C18) and copy down a
couple of rows. *In cell C18, use this array formula (Ctrl+Shift+Enter)

=LARGE(($C$3:$C$14=LOOKUP(REPT("z",99),C$18:C18))* ($D$3:$D$14=LOOKUP(REPT("*z",99),D$18:D18))*($E$3: $E$14),COUNTIF(G$18:G18,G18))

Now you can copy this formula down as many rows. *Also, keep adding more
criteria such as TV and Tuesday

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

wrote in message

...



I am trying to return the top N values from a list with corresponding
names. *The criteria for ranking changes.
ColA:C contain my data (thousands of rows). *ColD:E contain my
criteria (note below I want to return top 3 values). *ColF sorts in
Top3 order.

ColA * * * * * * *ColB * * * * * * *ColC
ColD * * * * * *ColE * * * * * * ColF

Movie * * * * * *Friday * * * * * * $25
Movie * * * * * Friday * * * * * $26
Movie * * * * * *Friday * * * * * * $26
Movie * * * * * Friday * * * * * $25
Movie * * * * * *Friday * * * * * * $18
Movie * * * * * Friday * * * * * $18
TV * * * * * * * *Tues * * * * * * * $12
Movie * * * * * Tues * * * * * * $21
TV * * * * * * * *Tues * * * * * * * $19
Movie * * * * * Tues * * * * * * $19
TV * * * * * * * *Tues * * * * * * * $21
Movie * * * * * Tues * * * * * * $12

I would like to use a formla that can rank the data when the two
criteria in ColD & E are met. *So when Movie and Friday show up in the
list of thousands, just return the top 3 values. *Then when Movie and
Tues are met, return the top 3 values.

I know this can be done, but I am having trouble fixing my formulas
when the two criteria change.

Thanks in advance for your help.- Hide quoted text -

- Show quoted text -

Thanks for your help Ashish. That's what I needed.

Conor

Return Top values with changing criteria
 

You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
On May 19, 7:59 pm, "Ashish Mathur" wrote:
Hi,

Try this.

Assume your data is in range C3:E14. In cell C18, enter Movie and in
cell
D18 enter Tuesday. In cell G18, enter =IF(C18="",G17,C18) and copy down
a
couple of rows. In cell C18, use this array formula (Ctrl+Shift+Enter)

=LARGE(($C$3:$C$14=LOOKUP(REPT("z",99),C$18:C18))* ($D$3:$D$14=LOOKUP(REPT("*z",99),D$18:D18))*($E$3: $E$14),COUNTIF(G$18:G18,G18))

Now you can copy this formula down as many rows. Also, keep adding more
criteria such as TV and Tuesday

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

wrote in message

...



I am trying to return the top N values from a list with corresponding
names. The criteria for ranking changes.
ColA:C contain my data (thousands of rows). ColD:E contain my
criteria (note below I want to return top 3 values). ColF sorts in
Top3 order.

ColA ColB ColC
ColD ColE ColF

Movie Friday $25
Movie Friday $26
Movie Friday $26
Movie Friday $25
Movie Friday $18
Movie Friday $18
TV Tues $12
Movie Tues $21
TV Tues $19
Movie Tues $19
TV Tues $21
Movie Tues $12

I would like to use a formla that can rank the data when the two
criteria in ColD & E are met. So when Movie and Friday show up in the
list of thousands, just return the top 3 values. Then when Movie and
Tues are met, return the top 3 values.

I know this can be done, but I am having trouble fixing my formulas
when the two criteria change.

Thanks in advance for your help.- Hide quoted text -

- Show quoted text -

Thanks for your help Ashish. That's what I needed.

Conor