Table of Contents


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 D2:

=INDEX($A$2:$A$8, MATCH(MAX(IF(NOT(COUNTIF($D$1:D1, $A$2:$A$8)),SUMIF($A$2:$A$8, "="&$A$2:$A$8, $B$2:$B$8),"")), IF(NOT(COUNTIF($D$1:D1, $A$2:$A$8)),SUMIF($A$2:$A$8, "="&$A$2:$A$8, $B$2:$B$8),""), 0))

How to create an array formula

  1. Copy above array formula
  2. Select cell D2
  3. Click in formula bar
  4. Paste array formula
  5. Press and hold Ctrl + Shift
  6. Press Enter
How to copy array formula
  1. Select cell D2
  2. Copy cell D2 (Ctrl + )
  3. Select cell range D3:D7
  4. Paste (Ctrl + v)

Formula in cell E2:

=SUMIF($A$2:$A$8,D2,$B$2:$B$8)

Download excel *.xlsx file

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

Filtering unique distinct text values and sort them based on sum of adjacent values using a criteria list

Array formula in cell E7:

=INDEX($A$2:$A$8, MATCH(MAX(IF(NOT(COUNTIF($D$6:D6, $A$2:$A$8)+(COUNTIF($E$1:$E$2,$A$2:$A$8)=0)),SUMIF($A$2:$A$8, "="&$A$2:$A$8, $B$2:$B$8),"")), IF(NOT(COUNTIF($D$6:D6, $A$2:$A$8)+(COUNTIF($E$1:$E$2,$A$2:$A$8)=0)),SUMIF($A$2:$A$8, "="&$A$2:$A$8, $B$2:$B$8),""), 0))

Formula in cell E7:

=SUMIF($A$2:$A$8,D7,$B$2:$B$8)

Download excel *.xlsx file

filter-unique-distinct-list-sorted-based-on-sum-of-adjacent-values-using-array-formula2.xlsx

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:

Create unique distinct list sorted based on text length using array formula in excel

Unique distinct list sorted based on occurrance in a column in excel

Excel 2007/2010 array formula: Filter unique distinct values, sorted and blanks removed

Filter a column and create a new unique list sorted from A to Z using array formula in excel

Unique distinct list from a column sorted A to Z using array formula in excel