## Filter unique distinct list sorted based on sum of adjacent values using array formula in excel

**Table of Contents**

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

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

**How to create an array formula**

- Copy above array formula
- Select cell D2
- Click in formula bar
- Paste array formula
- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula**

- Select cell D2
- Copy cell D2 (Ctrl + )
- Select cell range D3:D7
- Paste (Ctrl + v)

**Formula in cell E2:**

### 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:**

**Formula in cell E7:**

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

### Category: Sorted unique distinct values

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]

Comments(53) Filed in category: Excel, Sorted unique distinct values

Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]

Comments(24) Filed in category: Excel, Sorted unique distinct values

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

Comments(21) Filed in category: Excel, Sorted unique distinct values

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

Comments(15) Filed in category: Excel, Sorted unique distinct values

Table of Contents Filter unique distinct values, sorted and blanks removed Filter duplicate values, sorted and blanks removed EEK asks: […]

Comments(11) Filed in category: Excel, Sorted unique distinct values

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Answer: […]

Comments(6) Filed in category: Excel, Sorted unique distinct values

Eddie asks: Find and Sort I have question, in range A1,A2,A3,A4 contain 097 494 861 575. What is the formula […]

Comments(6) Filed in category: Excel, Sorted unique distinct values

Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on […]

Comments(3) Filed in category: Excel, Sorted unique distinct values

Inspired from a comment in this article Unique values from multiple columns using array formulas I have now created this […]

Comments(3) Filed in category: Excel, Sorted unique distinct values, Unique distinct values

### 10 Responses to “Filter unique distinct list sorted based on sum of adjacent values using array formula in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[...] Filter unique distinct list sorted based on sum of adjacent values … [...]

How might I go about doing this if the values range from positive to negative. ie.

Text Number

AA 60

BB 90

CC 80

EE -100

CC 30

FF -50

DD 100

Eric,

Array formula in A11:

=INDEX(List_text, MATCH(LARGE(IF(COUNTIF($A$10:A10, List_text)=0, SUMIF(List_text, "="&List_text, List_number), ""), 1), SUMIF(List_text, "="&List_text, List_number)*NOT(COUNTIF($A$10:A10, List_text)), 0)) + CTRL + SHIFT + ENTER. Copy cell A11 and paste it down as far as necessary.

Hi Oscar,

Thanks for the formula above, it fixes the case where there are negative values. But, I'm seeing one error - whenever the sum of numbers for some entry is Zero, then the formula fails. In Eric's example set above, if number corresponding to DD was 0, then your formula stops to work. Can you pls suggest something to fix this? Many thanks in adv, appreciate if you could respond soon. Cheers.

William,

You are right! I have changed this post. I hope it works better now.

Thanks for commenting!

What if i wanted sum value to be sorted in descending order.

Oscar, one more question.

How to modify this formula, to use other unique criteria list array being on another worksheet? For example: unique values match only are - AA and BB from criteria list and A1:A12 range from your example.

Can you help me?

Bill,

read this:

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

Thank you, Oscar!

Hi, Oscar,

So what if to do this single array formula? Is it real? Without prepared criteria list. The criteria list mixed with the others

values. How to extract the same values from other range contained in two ranges and sorted by SUM using single formula?