Article updated on February 16, 2018

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:


Download excel *.xlsx file

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


Download excel *.xlsx file


Functions in this article:

Counts the number of cells within a range that meet the given condition

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

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

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