Add largest Values

[damorrison]

I have a list something like this

A C
2 22
3 25
4 36
11 38
9 45
4 36
5 22
2 45
5 25

I would like to Sum the values in column A according to the values in
column C

Add the 1st largest of column C
45=11
2nd largest
38=11
3rd largest
36=8
ect
when I use the =large() formula and there is more than one number, well
it doesn't work, there are 2 -45's so the 3rd largest number is 38 when
it should be the 2nd largest

Thanks

 

[Guest]

look at the rank function,there is a section there on how to rank ties.Hope
you can use it!

 

[curlydave]

you will need an additional 3 columns for this to work

Assuming row 1 is a heading row

In column D insert this formula

=IF(COUNTIF($C$2:C2,C2)=1,RANK(C2,$C$2:$C$10,1),"")
=IF(COUNTIF($C$2:C3,C3)=1,RANK(C3,$C$2:$C$10,1),"")
=IF(COUNTIF($C$2:C4,C4)=1,RANK(C4,$C$2:$C$10,1),"")
=IF(COUNTIF($C$2:C5,C5)=1,RANK(C5,$C$2:$C$10,1),"")
=IF(COUNTIF($C$2:C6,C6)=1,RANK(C6,$C$2:$C$10,1),"")
=IF(COUNTIF($C$2:C7,C7)=1,RANK(C7,$C$2:$C$10,1),"")
=IF(COUNTIF($C$2:C8,C8)=1,RANK(C8,$C$2:$C$10,1),"")
=IF(COUNTIF($C$2:C9,C9)=1,RANK(C9,$C$2:$C$10,1),"")
=IF(COUNTIF($C$2:C10,C10)=1,RANK(C10,$C$2:$C$10,1),"")

In column E insert this formula

=IF(D2<>"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUNT($D$22)),$D$2:$D$10,0)-1,0),"")
=IF(D3<>"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUNT($D$23)),$D$2:$D$10,0)-1,0),"")
=IF(D4<>"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUNT($D$24)),$D$2:$D$10,0)-1,0),"")
=IF(D5<>"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUNT($D$25)),$D$2:$D$10,0)-1,0),"")
=IF(D6<>"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUNT($D$26)),$D$2:$D$10,0)-1,0),"")
=IF(D7<>"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUNT($D$27)),$D$2:$D$10,0)-1,0),"")
=IF(D8<>"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUNT($D$28)),$D$2:$D$10,0)-1,0),"")
=IF(D9<>"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUNT($D$29)),$D$2:$D$10,0)-1,0),"")
=IF(D10<>"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUNT($D$210)),$D$2:$D$10,0)-1,0),"")


In column F insert this formula

=IF(D2<>"",SUMIF($C$2:$C$10,E2,$A$2:$A$10),"")
=IF(D3<>"",SUMIF($C$2:$C$10,E3,$A$2:$A$10),"")
=IF(D4<>"",SUMIF($C$2:$C$10,E4,$A$2:$A$10),"")
=IF(D5<>"",SUMIF($C$2:$C$10,E5,$A$2:$A$10),"")
=IF(D6<>"",SUMIF($C$2:$C$10,E6,$A$2:$A$10),"")
=IF(D7<>"",SUMIF($C$2:$C$10,E7,$A$2:$A$10),"")
=IF(D8<>"",SUMIF($C$2:$C$10,E8,$A$2:$A$10),"")
=IF(D9<>"",SUMIF($C$2:$C$10,E9,$A$2:$A$10),"")
=IF(D10<>"",SUMIF($C$2:$C$10,E10,$A$2:$A$10),"")


Would be nice to be able to nest all the formulas but I don't think it
is possible