Return Top values with changing criteria

C

conorfinnegan

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.
 
R

RagDyer

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)))
 
R

RagDyer

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)))
 
A

Ashish Mathur

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
 
A

Ashish Mathur

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
 
C

conorfinnegan

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 !
---------------------------------------------------------------------------











- Show quoted text -
Click to expand...

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
 
C

conorfinnegan

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 !
---------------------------------------------------------------------------











- Show quoted text -
Click to expand...

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
 
R

RagDyer

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 !
---------------------------------------------------------------------------

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 !
---------------------------------------------------------------------------











- Show quoted text -
Click to expand...

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
 
R

RagDyer

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 !
---------------------------------------------------------------------------

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 !
---------------------------------------------------------------------------











- Show quoted text -
Click to expand...

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
 
C

conorfinnegan

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 !
---------------------------------------------------------------------------







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 -
Click to expand...

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
 
C

conorfinnegan

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 !
---------------------------------------------------------------------------







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 -
Click to expand...

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
 
R

RagDyer

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 !
---------------------------------------------------------------------------
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 !
---------------------------------------------------------------------------







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 -
Click to expand...

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
 
R

RagDyer

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 !
---------------------------------------------------------------------------
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 !
---------------------------------------------------------------------------







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 -
Click to expand...

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
 
A

Ashish Mathur

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
 
A

Ashish Mathur

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
 
C

conorfinnegan

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 copydown 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











- Show quoted text -
Click to expand...

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

Conor
 
C

conorfinnegan

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 copydown 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











- Show quoted text -
Click to expand...

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

Conor
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countif Function?? 3
WCG Stats Friday 30 June 2023 3
WCG Stats Friday 28 July 2023 3
WCG Stats Friday 01 September 2023 3
WCG Stats Friday 14 July 2023 4
WCG Stats Friday 07 April 2023 2
WCG Stats Friday 27 January 2023 2
WCG Stats Friday 28 April 2023 3

Top