Question: How do I create a distinct list from another list and then sum adjacent value and sort distinct list from large to small? (See picture to the right)

Answer: I think you can do that with Pivot tables. But here is a formula to sort distinct values from large to small, see picture below (C2:C10):

=INDEX(List, MATCH(LARGE(SUMIF(List, INDEX(List, SMALL(IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), MATCH(List, List, 0), ""), ROW(List)-ROW(List_start)+1)), List_amount), ROW(List)-ROW(List_start)+1), SUMIF(List, List, List_amount), 0)) + Ctrl + Shift + Enter

Formula in (D2:D10):

EDIT: =SUMIF(List;C2;List_amount) + Ctrl + Shift + Enter copied down to D10.

=IF(ROW(List)-ROW(List_start)+1<=COUNT(IF(MATCH(List, List, 0)=ROW(List)-ROW(List_start)+1, MATCH(List, List, 0), "")), LARGE(SUMIF(List, INDEX(List, SMALL(IF(MATCH(List, List, 0)=ROW(List)-ROW(List_start)+1, MATCH(List, List, 0), ""), ROW(List)-ROW(List_start)+1)), List_amount), ROW(List)-ROW(List_start)+1), "") + Ctrl + Shift + Enter

There are some named ranges in this article:

List (A2:A10)

List_start (A2)

List_amount (B2:B10)

To customize formulas to your workbook, change named ranges.

Download excel sample file for this article.
unique-list-sorted-by-size.xls
(Excel 97-2003 Workbook *.xls)

The downside with this formula is that if there are two or more summed values that are identical , the wrong value will be matched in column A. Resulting not creating a distinct list. This problem might be solved in an upcoming article.

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

LARGE(array,k) returns the k-th largest row number in this data set

Related posts:

1. Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
2. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
3. Create a list of duplicates where adjacent cell value meets a condition in excel
4. Create a unique distinct list and sort by occurrances from large to small
5. Create unique distinct list from column where an adjacent column meets criteria
6. Unique list to be created from a column where an adjacent column has text cell values
7. Create a unique distinct list from a date range in excel
8. How to create a unique distinct list where other columns meet two criteria
9. Extract distinct unique sorted year and month list from a date series in excel
10. Create unique distinct list sorted based on text length using array formula in excel