excel formula to remove duplicates and sum adjacent values

Under Columns, select one or more columns. On the Data tab, click Remove Duplicates (in the Data Tools group). Select the range of cells, or ensure that the active cell is in a table.

When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted.īecause you are permanently deleting data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values. Other values outside the range of cells or table will not change or move. Do you know any other method? Or do you have any questions? Feel free to ask us.When you remove duplicate values, the only effect is on the values in the range of cells or table. Using these methods, you can sum all matches with VLOOKUP in Excel. Finally, the SUM function returns the sum of all the prices that match the l ookup values.Therefore it matches the lookup values with all the values of the first column A4:A13, and returns the corresponding values from the 4th column ( Price).ROW(A1:INDIRECT(“A”&COUNTIF(C4:C13,F4)))becomes ROW(A1:A3) and returns an array.COUNTIF(C4:C13,F4) returns 3, as there are a total of 3 cells in the range C4:C13 ( Author) that contain the lookup value F4 ( Charles Dickens).Look, it returns the sum of the prices of all the books of Charles Dickens, $52.00.

The sum of the prices of all the books of Charles Dickens can be found using this formula:

If you use an older version of Excel, you can use the IF function of Excel to sum all the matches from any data set. Use IF Function to Sum All Matches with VLOOKUP in Excel (For Older Versions of Excel)

You can change the lookup value to any other author except Charles Dickens in cell F4, and it will return the total price of the books of that author.Ģ.

Finally, SUM(FILTER(D4:D13,C4:C13=F4)) returns the sum of all the prices of all the books returned by the FILTER function.

FILTER(D4:D13,C4:C13=F4) matches all values of the column C4:C13 (Author) with F4 ( Charles Dickens) and returns the corresponding values from the column D4:D13 ( Price).

Here F4 ( Charles Dickens) is our lookup value, C4:C13 (Author) is the lookup column, and D4:D13 (Price) is the other column.

The FILTER function matches a lookup value with all the values of a lookup column and returns the corresponding values from another column.

Excel formula to remove duplicates and sum adjacent values