Filtering unique distinct text values and sort them based on sum of adjacent values can be done by creating a pivot table. But in this blog post I want to show how to do this, using an excel array formula.

Array formula in A11:

=INDEX(List_text, MATCH(MAX(SUMIF(List_text, "="&List_text, List_number)*NOT(COUNTIF($A$10:A10, List_text))), SUMIF(List_text, "="&List_text, List_number)*NOT(COUNTIF($A$10:A10, List_text)), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

Formula in B11:

=SUMIF(List_text, A11, List_number) + ENTER copied down as far as necessary.

Named ranges

List_text (A2:A8)
List_number (B2:B8)
What is named ranges?

How to implement array formula to your workbook

Change the named ranges. If your list starts at, for example, F3. Change $A$10:A10 in the above formulas to F2:$F$2.

Download excel sample file for this tutorial.

Filter unique distinct list sorted based on sum of adjacent values.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

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

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

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

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

ROW(reference) returns the rownumber of a reference

SUMIF(range, criteria, sum_range)
Adds the cells specified by a given condition or criteria

Related posts:

1. Create unique distinct list sorted based on text length using array formula in excel
2. Filter a column and create a new unique list sorted from A to Z using array formula in excel
3. Unique distinct list sorted based on occurrance in a column in excel
4. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
5. Unique distinct list from a column sorted A to Z using array formula in excel
6. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
7. Filter unique distinct values where adjacent cells contain search string in excel
8. Filter unique values from a range using array formula in excel
9. Filter unique rows and sort by date using array formula in excel
10. Filter common values between two ranges using array formula in excel