## Sum unique values in excel

**Question: **I have a list of values, and i want to sum only unique values, how?

**Answer: **

If unique values are values occuring only once in a a list, here is the formula to sum unique values.

### Array formula in cell I1:

Number 3 is the only unique value.

If unique values are values occuring twice or more but are to be summed only once, here is the formula**. **(Duplicates numbers are removed.)

### Array formula in cell E1:

Number 2, 3 , 5, 6, 7 are unique distinct values. 2 + 3 + 5 + 6 + 7 = 23.

### Named ranges

List = A2:A11

List_start = A2.

Here is a picture from the excel file attached below the picture.

### Download excel example file.

sum-distinct-values

(Excel 97-2003 Workbook *.xls)

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

**COUNT(**value1;[value2]**)
**Counts the number of cells in a range that contain numbers

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

Returns the rownumber of a reference

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

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Count cells between a given value

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]

Filter unique values and sort based on adjacent date

Question: How do I filter unique rows and sort by date? Answer: Column A and B are the original list. […]

Extract unique values from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you […]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]

Extract unique values from a range

Question: How do I extract values only occurring once in a range? Answer: A range (tbl_text) containing text values Array […]

Extract unique and duplicate numbers from multiple sheets

By coincidence I seem to have created a "unique" formula in this post: Combine data from multiple sheets in excel […]

Filter unique values from a range using array formula in excel

Unique values in a range are values occurring only once. This is what I am going to do in this […]

### 4 Responses to “Sum unique values in excel”

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

I have given up trying to figure out the following problem as it seems over my head, and am

hoping there is a kind soul out there can help me out with the solution.

I am trying to setup a spreadsheet to make calculations which are then exported to Quickbooks.

The original data is returned to Excel from an Access database by msq. Each record relates to an order number and is tied to a

product, and there may be more than entry for each order number as an order may have more than one product.

The actual data import sheet looks something like this:

OrderNumber ItemNumber Date Product PricePerUnit CostPerUnit

4967 1 10-May-10 widget 430.92 306.00

4967 2 10-May-10 doohickey 256.33 165.00

4968 1 11-May-10 doohickey 256.33 165.00

The actual data export sheet looks something like this:

Date Name Account Account Type MEMO Split Account Amount

10-May-10 vendor1 Bank Credit Card 4967 Cost of Goods Sold -306.00

10-May-10 vendor1 Bank Credit Card 4967 Cost of Goods Sold -165.00

11-May-10 vendor2 Bank Credit Card 4968 Cost of Goods Sold -165.00

My problem is that I cannot figure out how to return the total only, and not individual amounts, for orders

with more than 1 item. I.E., how can I tell excel to return each OrderNumber only once, along with

the total for each, like this:

Date Name Account Account Type MEMO Split Account Amount

10-May-10 vendor1 Bank Credit Card 4967 Cost of Goods Sold -471.00

11-May-10 vendor2 Bank Credit Card 4968 Cost of Goods Sold -165.00

There is a little more to this, as I am also trying to include shipping costs, but I Am still trying to

figure out how to return the data using msq, which I am finding a little quirky to use at all, but I have

managed to at least get it to work on a basic level.

Anyhow, is there anyone there who can help? I would be very grateful and much obliged.

Thanks in advance.

AJ

AJ,

see this post: https://www.get-digital-help.com/2010/05/22/how-to-return-the-total-for-orders-with-more-than-one-item-in-excel/

Hi!

Let me say "Thanks" to you because you saved me, my next degree and my huge worksheet!

After countless hours looking for a feasible solution, I finally got here. May you be praised! :)

alemar,

Thanks!