Author: Oscar Cronquist Article last updated on December 06, 2018

Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by creating a pivot table, and I highly recommend it.

But in this blog post I want to show how to do this using an Excel array formula.

Array formula in E2:

=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. Double press with left mouse button on cell E3
  3. Paste array formula
  4. Press and hold Ctrl + Shift simultaneouslöy
  5. Press Enter

Formula in cell F3:

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

Explaining formula in cell E2

Step 1 - Count prior values above current cell

The COUNTIF function counts values based on a condition or criteria, if number is 0 (zero) then the corresponding value has not yet been displayed.

NOT(COUNTIF($D$1:D1, $A$2:$A$8))

becomes

NOT(COUNTIF("Unique distinct", {"AA";"BB";"CC";"BB";"CC";"AA";"DD"}))

becomes

NOT({0;0;0;0;0;0;0})

The NOT function returns the boolean opposite to the given argument. The array contains no boolean values, however it does contain their numerical equivavents. TRUE = 1 and FALSE = 0 (zero).

NOT({0;0;0;0;0;0;0})

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 2 - IF TRUE then return the corresponding sum

The IF function returns the total if boolean value is TRUE. FALSE returns "" (nothing).

IF(NOT(COUNTIF($E$2:E2, $B$3:$B$9)),SUMIF($B$3:$B$9, "="&$B$3:$B$9, $C$3:$C$9),"")

becomes

IF({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, SUMIF($B$3:$B$9, "="&$B$3:$B$9, $C$3:$C$9),"")

The SUMIF function adds numbers and returns a total based on a condition or criteria.

IF({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, SUMIF($B$3:$B$9, "="&$B$3:$B$9, $C$3:$C$9),"")

becomes

IF({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, {80;100;110;100;110;80;100},"")

and returns

{80;100;110;100;110;80;100}.

Step 3 - Get the largest number in array

The MAX function returns the largest number in array ignoring blanks and text values.

MAX(IF(NOT(COUNTIF($E$2:E2, $B$3:$B$9)),SUMIF($B$3:$B$9, "="&$B$3:$B$9, $C$3:$C$9),""))

becomes

MAX({80;100;110;100;110;80;100})

and returns 110.

 Step 4 - Match number

The MATCH function returns the relative position of a value in a cell range or array.

MATCH(MAX(IF(NOT(COUNTIF($E$2:E2, $B$3:$B$9)),SUMIF($B$3:$B$9, "="&$B$3:$B$9, $C$3:$C$9),"")), IF(NOT(COUNTIF($E$2:E2, $B$3:$B$9)),SUMIF($B$3:$B$9, "="&$B$3:$B$9, $C$3:$C$9),""), 0)

becomes

MATCH(110, IF(NOT(COUNTIF($E$2:E2, $B$3:$B$9)),SUMIF($B$3:$B$9, "="&$B$3:$B$9, $C$3:$C$9),""), 0)

becomes

MATCH(110, {80;100;110;100;110;80;100}, 0)

and returns 3.

Step 5 - Get value

The INDEX function returns a value based on row number (and column number if needed)

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))

becomes

INDEX($A$2:$A$8, 3)

and returns "CC" in cell E2.

Get Excel *.xlsx file

Filter unique distinct list sorted based on sum of adjacent values.xlsx

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)

Get excel *.xlsx file

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

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles