Learn How to Sum Without Duplicates in Excel with These Simple Steps

Sometimes, you may want to sum a range of cells that contain some duplicate values, but only count each unique value once. For example, if you have a list of sales amounts for different products, and some products appear more than once, you may want to get the total sales amount for each unique product. How can you do this in Excel?

There are a few ways to achieve this goal, depending on your data structure and preference. In this article, we will introduce three methods to sum without duplicate calculation in excel formula:

• Method 1: Use the SUMPRODUCT and COUNTIF functions
• Method 2: Use the SUMIFS function with a helper column
• Method 3: Use the Remove Duplicates feature and the SUM function

Method 1: Use the SUMPRODUCT and COUNTIF functions

The SUMPRODUCT function can multiply corresponding elements in two or more arrays and return the sum of the products. The COUNTIF function can count the number of cells that meet a certain criterion. By combining these two functions, we can create a formula that sums the values without duplicates.

The general formula is:

=SUMPRODUCT(–(COUNTIF(range,range)=1),values)

where range is the range of cells that contain the duplicate values, and values is the range of cells that contain the values to be summed.

The formula works as follows:

• The COUNTIF function counts the number of times each value in the range appears, and returns an array of frequencies.
• The comparison operator =1 checks if each frequency is equal to 1, and returns an array of TRUE or FALSE values.
• The double hyphen – converts the TRUE or FALSE values into 1 or 0, respectively.
• The SUMPRODUCT function multiplies the array of 1 or 0 by the array of values, and returns the sum of the products.

Example

Suppose we have the following data in Excel:

We want to sum the amount column without counting the duplicate products. We can use the formula:

=SUMPRODUCT(–(COUNTIF(A2:A8,A2:A8)=1),B2:B8)

The formula returns 1200, which is the sum of 100, 300, 400, and 500.

Method 2: Use the SUMIFS function with a helper column

The SUMIFS function can sum the values in a range that meet multiple criteria. To use this function, we need to create a helper column that indicates whether a value is unique or not. We can use the COUNTIF function to do this.

The general formula for the helper column is:

=IF(COUNTIF(range,cell)=1,1,0)

where range is the range of cells that contain the duplicate values, and cell is the current cell in the range.

The formula works as follows:

• The COUNTIF function counts the number of times the current cell value appears in the range, and returns a frequency.
• The IF function checks if the frequency is equal to 1, and returns 1 if true, or 0 if false.

The general formula for the SUMIFS function is:

=SUMIFS(sum_range,criteria_range,criteria)

where sum_range is the range of cells that contain the values to be summed, criteria_range is the range of cells that contain the criteria, and criteria is the value that the cells in the criteria_range must match.

The formula works as follows:

• The SUMIFS function sums the values in the sum_range that meet the criteria in the criteria_range.
• The criteria is 1, which means we only sum the values that have 1 in the helper column, indicating they are unique.

Example

Using the same data as before, we can create a helper column in column C with the formula:

=IF(COUNTIF(A2:A8,A2)=1,1,0)

and copy it down to the other cells. The result is:

We can then use the formula:

=SUMIFS(B2:B8,C2:C8,1)

The formula returns 1200, which is the same as the previous method.

Method 3: Use the Remove Duplicates feature and the SUM function

The Remove Duplicates feature can remove the duplicate values from a range of cells, leaving only the unique values. The SUM function can then sum the remaining values.

The steps are:

• Select the range of cells that contain the duplicate values and the values to be summed.
• Go to the Data tab, and click the Remove Duplicates button in the Data Tools group.
• In the Remove Duplicates dialog box, check the column that contains the duplicate values, and click OK.
• Excel will remove the duplicate rows, and display a message indicating how many rows were removed and how many rows remain.
• Use the SUM function to sum the remaining values in the column.

Example

Using the same data as before, we can select the range A2:B8, and click the Remove Duplicates button. We check the Product column, and click OK. Excel will remove four rows, and leave four rows. The result is:

We can then use the formula:

=SUM(B2:B5)

The formula returns 1500, which is the sum of all the unique products.

Note: This method will change the original data, so you may want to make a copy of the data before using it.