## Sum unique numbers

The formula in cell D3 adds all unique numbers in cell range B3:B12 and returns the total. Unique values are all values occurring only once in the list.

Formula in cell D3:

### Explaining formula in cell D3

#### Step 1 - Count occurrence of each value in cell range

The COUNTIF function is an extremely useful function, this time I am counting how many times each value shows up in the cell range.

COUNTIF(B3:B12,B3:B12)

becomes

COUNTIF({40; 10; 80; 40; 60; 90; 40; 20; 20; 60},{40; 10; 80; 40; 60; 90; 40; 20; 20; 60})

and returns

{3;1;1;3;2;1;3;2;2;2}

#### Step 2 - Check if value is unique

The equal sign checks if number is equal to 1.

COUNTIF(B3:B12,B3:B12)=1

becomes

{3;1;1;3;2;1;3;2;2;2}=1

and returns

{FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}

#### Step 3 - Multiply with cell range

The boolean values in the array show which numbers to include in sum. FALSE is the same as 0 (zero) and TRUE is equivalebt to 1. The parentheses make sure that the comparison is calculated before multiplying.

(COUNTIF(B3:B12,B3:B12)=1)*B3:B12

becomes

{FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}*B3:B12

becomes

{FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE}*{3;1;1;3;2;1;3;2;2;2}

and returns

{0;10;80;0;0;90;0;0;0;0}

#### Step 4 - Sum numbers

The SUMPRODUCT function now simply adds the numbers and returns the total.

SUMPRODUCT((COUNTIF(B3:B12,B3:B12)=1)*B3:B12)

becomes

SUMPRODUCT({0;10;80;0;0;90;0;0;0;0})

and returns 180 in cell D3.

Why use the SUMPRODUCT function and not the SUM function? You don't need to enter this formula as an array formula if you use the SUMPRODUCT function.

### Download Excel *.xlsx file

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

Running totals based on criteria

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell? Can I have […]

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

### 5 Responses to “Sum unique numbers”

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

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!

How to calculate sum of unique values if the data is in another worksheet.

I get zero as the answer.

Any solutions pls