Author: Oscar Cronquist Article last 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:

=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