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
|