# How to Sum/Calculate Total Values with Duplicate Values Only Once in Excel

In statistic when calculating the total values for a range of numbers, we often count duplicate values only once. Normally, if only apply SUM function to calculate total values, it will cover all values including all duplicate values. So, we need to update the formula to make it meet our requirement. This free tutorial will show you the formula to sum values with duplicate values only once in the selected range.

Precondition:

See screenshot below. We want to sum all values but for the duplicate values, we only count once.

## 1. Calculate Total Values with Counting Duplicate Values Only Once using Formula

Step1: In B7, enter the formula:

``=SUMPRODUCT(A2:B6/COUNTIF(A2:B6,A2:B6))``

In this formula, A2:B6 is the range selected for calculating.

Step2: Click Enter to get result. Verify that the sum value is calculated properly. Duplicate values like 100, 60 are only count once.

## 2. Calculate Total Values with Counting Duplicate Values Only Once using User Defined Function

You can create a User-Defined Function (UDF) in VBA to calculate the total values of a range while counting duplicate values only once. Here are the steps of how to do it:

Step1: press ALT + F11 to open the VBA editor in your workbook.

Step2: In the editor, go to Insert > Module to create a new module.

Step3: In the module, paste the following code. Save the module and go back to your Excel workbook.

``````Function SumUnique_ExcelHow(ByVal rng As Range) As Double
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Dim cell As Range
For Each cell In rng
If Not dict.exists(cell.Value) Then
End If
Next cell

Dim key As Variant
For Each key In dict.keys
SumUnique_ExcelHow = SumUnique_ExcelHow + key
Next key
End Function``````

Step4: In any blank cell, enter the formula:

``=SumUnique_ExcelHow(A2:B6)``

Where A2:B6 is the range of values you want to calculate the total for.

Note: The User Defined Function loops through each cell in the range and adds its value to the dictionary object only if it doesn’t already exist as a key. Finally, the User Defined Function loops through the dictionary keys and adds up the total value, which is returned as the function result.

## 3. Video: Calculate Total Values with Counting Duplicate Values Only Once

This video will show you how to calculate total values with counting duplicate values only once using either the SUMPRODUCT function or a user-defined function with VBA code.

## 4. Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…