## Sum unique distinct numbers

The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique distinct numbers meaning the first instance of each number is added to the total, however, the duplicates are ignored.

### Explaining formula in cell D12

If you want to examine the formula yourself then go to tab "Formula" on the ribbon and then click "Evaluate Formula" button.

This will show the calculation steps, one at a time. Simply click the Evaluate button to move to next calculation step.

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

The COUNTIF function counts each value in the cell range and returns an array of numbers which shows the number of instances each value has.

COUNTIF(B3:B15,B3:B15)

becomes

COUNTIF({9;8;7;1;2;6;3;1;7;2;8;2;6},{9;8;7;1;2;6;3;1;7;2;8;2;6})

and returns

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

#### Step 2 - Divide 1 with array

If a number has a duplicate then there are two instances of that value. Divide 1 with 2 and we get 0.5, now multiply that value with the number and we get the number to add to the total.

1/COUNTIF(B3:B15,B3:B15)

becomes

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

and returns

{1;0.5;0.5;0.5;0.333333333333333;0.5;1;0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5}

#### Step 3 - Multiply with numbers

The parentheses make sure that the order of calculation is correct.

(1/COUNTIF(B3:B15,B3:B15))*B3:B15

becomes

{1;0.5;0.5;0.5;0.333333333333333;0.5;1;0.5;0.5;0.333333333333333;0.5;0.333333333333333;0.5}*B3:B15

and returns

{9;4;3.5;0.5;0.666666666666667;3;3;0.5;3.5;0.666666666666667;4;0.666666666666667;3}

#### Step 4 - Add numbers

The SUMPRODUCT function is a better option than the SUM function, we don't need to enter the formula as an array formula now.

SUMPRODUCT((1/COUNTIF(B3:B15,B3:B15))*B3:B15)

becomes

SUMPRODUCT({9;4;3.5;0.5;0.666666666666667;3;3;0.5;3.5;0.666666666666667;4;0.666666666666667;3})

and returns 36 in cell D12.

### 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, […]

The formula in cell D3 adds all unique numbers in cell range B3:B12 and returns the total. Unique values are all […]

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 […]

Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]

To extract groups from cell range B3:B10 I use the following regular formula in cell B13. Weekly Blog EMAIL Email Welcome! […]

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

The SUM function in cell D3 uses only a single cell reference and still manages to sum current and previous […]

The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]

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