## Sum unique values in excel

*Article updated on September 20, 2013*

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

Count how many numbers between a specific value occurring multiple times

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 […]Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel

By coincidence I seem to have created a "unique" formula in this post: Combine data from multiple sheets in excel […]### 4 Responses to “Sum unique values in excel”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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!