## Extract a unique distinct list and sum amounts based on a condition

**Anura asks:**

Is it possible to extend this by matching items that meet a criteria?

I have a list of credit card transactions showing the name of the card holder, their Branch and the amount. I want to produce a report for each Branch, so what I want is to extract only those people who match the Branch name. For example:

Frank Branch A

Frank Branch A

Frank Branch A

Joe Branch A

Mary Branch B

Jane Branch C

Mike Branch A

Joe Branch A

Dave Branch C

I would like a list of only those people for Branch A, and then be able to summarise the transactions. Or should I do this in two stages?

**Answer:**

This post demonstrates how to build a formula to extract a unique distinct list using a condition, however, I highly recommend a pivot table for this task: Pivot table - Unique distinct list

Type a branch in cell G2 and the formulas in cell range F5:H7 returns the correct records from B3:D12. If you have more than two values in one record (except the number) use the COUNTIFS function to filter unique distinct records.

**Array formula in F5:**

Copy cell F5 and paste to cell range F5:G7.

**Formula in cell H5:**

If you have a version earlier than Excel 2007 use the SUMPRODUCT function

If you are interested in how the SUMPRODUCT function works, read:

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

How to use the SUMPRODUCT function

### Watch a video where I explain the formula

Recommended link

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

5 easy ways to extract Unique Distinct Values

**How to enter an array formula**

- Copy (Ctrl + c) above formula
- Double press with left mouse button on cell E8
- Paste (Ctrl + v) to cell
- Press and hold CTRL + Shift simultaneously
- Press Enter
- Release all keys

Copy cell E8 and paste it down as far as needed. Copy cells and paste into cell range F8 and down as far as needed.

Recommended link

A beginners guide to Excel array formulas

Array formulas allows you to do advanced calculations not possible with regular formulas.

A beginners guide to Excel array formulas

### Explaining array formula in cell E8

**Step 1 - Check previous values to make sure they are not repeated**

COUNTIF($E$7:$E7, $A$2:$A$11)

If you examine the cell references you may find this strange: $E$7:$E7 It is an absolute and relative cell reference. It expands when the formula is copied across the spreadsheet. Read more here: Absolute and relative references in excel

COUNTIF($E$7:$E7, $A$2:$A$11)

becomes

COUNTIF("Name", {"Frank"; "Frank"; "Frank"; "Joe"; "Mary"; "Jane"; "Mike"; "Joe"; "Dave"; "Joe"})

and returns

{0;0;0;0;0;0;0;0;0;0}

Recommended article

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIF function

**Step 2 - Compare values in column B with the value in cell F1**

($B$2:$B$11<>$F$1)

becomes

{"Branch A"; "Branch A"; "Branch A"; "Branch A"; "Branch B"; "Branch C"; "Branch A"; "Branch A"; "Branch C"; "Branch A"}<>"Branch A"

and returns {FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}

**Step 3 - Add arrays**

COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1)

becomes

{0;0;0;0;0;0;0;0;0;0} + {FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}

and returns

{0;0;0;0;1;1;0;0;1;0}

**Step 4 - Find first 0 (zero) in array**

MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1), 0)

becomes

MATCH(0, {0;0;0;0;1;1;0;0;1;0}, 0)

and returns 1.

Recommended article

Identify the position of a value in an array.

**Step 5 - Return a value or reference of the cell at the intersection of a particular row and column**

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1), 0), COLUMN(A1))

becomes

=INDEX($A$2:$C$11, 1, COLUMN(A1))

becomes

=INDEX($A$2:$C$11, 1, 1)

becomes

=INDEX({"Frank","Branch A",10; "Frank","Branch A",20; "Frank","Branch A",40; "Joe","Branch A",30; "Mary","Branch B",5; "Jane","Branch C",10; "Mike","Branch A",15; "Joe","Branch A",40; "Dave","Branch C",35; "Joe","Branch A",20}, 1, 1)

and returns "Frank" in cell E8.

Recommended link

Gets a value in a specific cell range based on a row and column number.

### Get excel *.xls file

unique distinct list matching criteria.xls

(Excel 97-2003 Workbook *.xls)

Recommended articles:

Filter unique distinct records with a condition

Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]

Filter unique distinct records with a condition

Filter unique distinct values where adjacent cells contain search string

Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]

Filter unique distinct values where adjacent cells contain search string

Extract unique distinct values sorted based on sum of adjacent values

Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]

Extract unique distinct values sorted based on sum of adjacent values

### Functions in this article:

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

**SMALL(**array,k**)
**Returns the k-th smallest number in this data set.

**ROW(**reference**)**

Returns the rownumber of a reference

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

**MATCH(**lookup_value;lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**COUNTIF(**range,criteria**)**

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

**SUMPRODUCT(**array1, array2, **)**

Returns the sum of the products of the corresponding ranges or arrays

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

### 45 Responses to “Extract a unique distinct list and sum amounts based on a condition”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

This really is brilliant. Can I ask for one modification? How can I count the number of transactions per person?

Anura,

Formula in H8:

=SUMPRODUCT(--(E8=$A$2:$A$11)) + ENTER.

That is just awesome.

I tried equating the value "Branch A" itself in the formula instead of its address and it worked perfectly.

Thanks Oscar!

Vashi,

You are welcome!

Occar, your breakdown above worked for me, HOWEVER, i am using a date vs. Branch A. Needless to say, my spreadsheet is dynamic so i need the $F$1, in this scenario to map to a different page. When i simply change the formula to ='High Level Summary'!U3 (result = 1/10/2013 for example), my results turn to errors. It works fine as long as my date is just a date and not a formula. I even tried using INDIRECT prior to ='High Level Summary'!U3. A dropdown box is what dictates the date to be used. PLEASE HELP.... :(

Jana

Jana, can you provide the formula? Maybe your dates are not excel dates?

How excel stores dates

Oscar,

This is a pretty awesome solution!

I am trying to include your formula in an dashboard that shows dynamic results depending on the look-up value. I'm struggling with hiding error values (i.e. "#N/A") for the formula in the "Name" column.

I've already tried a few methods for hiding zero and error values that work with other formulas, but I can't seem to make anything work for your formula.

Can you help?

Thanks!

Dave

Looks like one way to do it is:

=IFERROR(INDEX($A$2:$C$11,MATCH(0,COUNTIF($E$7:$E7,$A$2:$A$11)+($B$2:$B$11$F$1),0),COLUMN(A1)),"")

This is a thing of beauty.

Very helpful - thank you!

dubdub,

thank you for commenting!

Thank you. This was very helpful as I wanted to extract personal data for league members (using the team as the criteria) from a league data sheet to a team data sheet.

I used David Myers' suggestion for ignoring #N/A error messages in empty rows but wonder if anyone can suggest a way to "hide" the zeros that appear when I have an incomplete row with empty cells. I assume I will need to use a nested if function in the column reference but can't get the syntax right.

I would really value any suggestions.

Thanks

Does this work if your data is on a different tab?

Tim,

Yes, change cell references.

Example,

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1), 0), COLUMN(A1))

becomes

=INDEX(sheet2!$A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, sheet2!$A$2:$A$11)+(sheet2!$B$2:$B$11<>$F$1), 0), COLUMN(A1))

Hi ,

Is these work on the more column. i have 1400 column and this formula not working.

data is much bigger.

I am want agent project list.

i want list of project on which agent has worked.

Regards,

Krunal

Krunal,

Yes, you need a custom function (vba).

how would you add another criteria, I am looking to do this with the date and time as the criteria to match.

john dalton,

If the start date is in F1 and end date is in F2 and the dates are in cell range B2:B11:

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+(($B$2:$B$11>$F$1)*($B$2:$B$11<$F$2)), 0), COLUMN(A1))

How do I add another additional criteria? Say if I wanted to pull a unique list of branches and invidiuals

Justine,

perhaps this is helpful:

Filter unique distinct row records in excel 2007

Hi Oscar,

I need help as well. Suppose that i needed to find the top 20 names with the highest amounts in a Branch. I've tried with the LARGE function but i can't make it work.

Thanks

Hello,

Thanks for this! It's saved me. However, I would like to modify this formula so that it doesn't summarize by name. So using your example, I would want to see Frank's name three times, Joe's name three times, etc. I want my unique distinct list to be the same as the original list, I just want to filter out those names that aren't Branch A.

Thanks.

Chuck

Array formula in cell E8:

=INDEX($A$2:$C$11, SMALL(IF($F$1<>$B$2:$B$11, MATCH(ROW($B$2:$B$11), ROW($B$2:$B$11)), ""), ROW(A1)), COLUMNS($A$1:A1))

Thanks, Oscar! Works like a champ. However, I would like to point out that this formula only works if your original table starts in A1. So the last part of your formula:

...ROW(A1)), COLUMNS($A$1:A1))

I had to come up with another way to get the correct values here. Once I did it worked great. Thank you.

Hi Oscar,

This works as a charm, however (as in previous comment) I need value be displayed all times it appears in the list. Is there any way to tell this formula not to ignore repetitive values?

I mean the result should look like

Frank Branch A

Frank Branch A

Frank Branch A

Joe Branch A

Mike Branch A

Joe Branch A

Joe Branch A

Thanks in advance!

I don't have any questions. I just want to say that you are awesome.

Thanks for your helpful and clear examples. Following your same example – how about if from a long list of branches, I need to extract the amounts of sub-set of branches. For examples, need the amounts from 7 out of 20 branches, just to illustrate an example. How do I name the string of non-consecutive branch numbers? Thanks again

Thanks for your helpful and clear examples. Following your same example – how about if from a long list of branches, I need to extract the amounts of sub-set of branches. For examples, need the amounts from 7 out of 20 branches, just to illustrate an example. How do I specify a string of non-consecutive branch numbers?

Sorry for the multiple entries, something is wrong on this end

Hi Oscar,

I dint understand how this countif is working. I can see that the range is smaller than the criteria. Can you please explain the logic through the first principles.

Thanks.

Hi Chuck

How can I sort my data while fetching. Lets say, I want my data to be sorted ascending order wise depending on amount. I don't want a help column. How can I do it in one go, while I am pulling the data using your formula

I have what I think is a very simple process I want to do, but I cannot find the answer on the internet because I do not know what to call it in 'excel speak'. I have a list of students and their reading level (numerical). I just want it to dynamically create lists of all the students in each level.

e.g.

Students Level

studentA 2

studentB 9

studentC 7

studentD 3

studentE 9

etc (in two columns) and I want to then create a table with the levels as the headings and the students names to automatically go under the level.

e.g.

1 2 3... ...9

studentA studentB

student E

How can I do this?

Hi Oscar,

I'm starting to learn Excel now. And I have a spreadsheet similar to a data entry. My job is to go through weekly data to see which kid is in trouble. My question is, Is there a formula I can set where it list down data from a certain range. For example if I want to view data from the 5/18/2016 to 5/24/2016.

Thanks

John

I recommend you convert your data table to an excel defined table and apply a date filter to your date column.

https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

Hi there,

I have a dataset and I wish to show, in a separate tab, a distinct list based on two criterion. What would I need to do?

Thanks, Kevin

Kevin,

This formula has one criterion (bolded):

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+

($B$2:$B$11<>$F$1), 0), COLUMN(A1))If you need two criteria, see this formula:

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+

($B$2:$B$11<>$F$1)*($A$2:$A$11<>"Frank"), 0), COLUMN(A1))It looks for a criterion in column A and another criterion in column B.

This formula looks for two criteria ($F$1:$F$2) in cell range $B$2:$B$11:

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+

COUNTIF($F$1:$F$2,"<>"&$B$2:$B$11), 0), COLUMN(A1))Hi Oscar,

I'm using a very similar formula to yours:

=IFERROR(INDEX(all_names, MATCH(0, IF($D$2=all_groups, COUNTIF(D$4:D4, all_names), ""), 0)),"")

This works well. However, if I try to change it to include an OR function it no longer work:

=IFERROR(INDEX(all_names, MATCH(0, IF(OR($D$2=all_groups,$D$2=all_groups_2,$D$2=all_groups_3), COUNTIF(D$4:D4, all_names), ""), 0)),"")

Any idea what I'm doing wrong?

Thanks,

Jake

Jake,

Try this:

=IFERROR(INDEX(all_names, MATCH(0, IF(($D$2=all_groups)+($D$2=all_groups_2)+($D$2=all_groups_3), COUNTIF(D$4:D4, all_names), ""), 0)),"")

Hello Oscar,

What code is needed to cause cells in Columns F - I to fill with the contents of Columns C - E when a cell in Column B includes a numeric value?

Liam,

See this post:

https://www.get-digital-help.com/2016/12/20/filter-rows-where-a-cell-contains-a-numeric-value/

THANK YOU SO MUCH.... IT IS VERY VERY VERY HELPFUL AND I AM TOO MUCH EXCITING TO LEARN THIS FORMULA..... I WOULD LIKE TO SAY AGAIN *"THANK YOU SO MUCH...."*

Dear

How can I make this work by having the names F5:F7 alphabetically with 2 conditions ?

Thanks

Oliver

[…] Liam asks: […]

[…] Extract a unique distinct list and sum amounts based on a condition […]

Oscar,

I have a list with duplicate values in 2 columns. How do I extract one distinct list with two columns. They are not two separate lists. They are columns with two different column headings: Type & Letter

Type Letter

Car A

Bike D

Car A

Bike D

Bike E

Result:

Car A

Bike D

Bike E

Car A

I made a mistake. The result should be

Type Letter

Car A

Bike D

Bike E