Microsoft Excel is a productivity software used to process various kinds of data in various fields. From households to corporate offices everywhere it is being used. It can help you in bookkeeping and analyzing data which if you want to calculate manually will take huge time and effort. While entering data there maybe sometimes when you need to input duplicate data (i.e. shopping cost of the same customer). But when aggregating data you will need summary data that will represent the total value of a particular entry (i.e. total shopping cost of a customer). So here we will learn how to Combine Duplicate Rows and Sum their Values in Excel.
About Practice Workbook
In this workbook we have a list containing dues of customers from 1st December, 2021 to 13th December, 2021. There are rows which contain the same customer on different dates. So what if you want to get an overall view of what amount of dues are for each customer. In this article we will see how this can be done.
Combine Duplicate Rows and Sum the Values in Excel ( 3 Easiest Ways)
1. Using Remove Duplicates and SUMIF Function
- Copy Customer name column (make sure you start copying from Header Customer) using CTRL+C or from the Ribbon.
- Paste it in a new cell.
- Now while selecting the copied cells go to Data Tab. Then from the Ribbon Data Tools > Remove Duplicates.
- A dialogue box for Remove Duplicates will appear. Make sure to mark My Data has Headers tick box. Select the listed columns (in our case, Customer) and then press OK.
- The duplicates have been removed !!
Now make a new header beside Customer naming it Total Due for sum.
- Select the Cell C5 underneath the new header and write the following function using SUMIF
which refers to calculating the summation value of F5 according to the data in D$5:D$17 corresponding to the names in the range of C$5:C$17. You can adjust the formula accordingly.
- Now copy this formula to next few cells by dragging it up to the cell where column of Customer ends. Done.
2. Using Consolidate
- Copy the headers of the original data and paste it where you want the consolidated data.
- Select the cell below the first copied header. Go to Data Tab. Then from the Ribbon Data Tools > Consolidate.
- A dialogue box for Consolidate will appear. In the Functions dropdown box select Sum (it should already be there). Don’t forget to mark the Left Column tick box.
- Now the most important part. Click into the Reference box and using a mouse select the cells without headers (it is very important that you do that) or you can manually input cells range (don’t forget to use $ to make cells absolute – i.e. in our example it is $C$5:$D$17. You know what? Use a mouse, that way excel will input it automatically). Then click OK.
You can also use this feature to consolidate data from multiple worksheets in the same workbook, and even multiple different workbooks.
3. Using Pivot Table
Pivot Table is a do it all kind of feature in excel. we can do all sorts of things with a Pivot Table – including consolidating our data set and removing the duplicates with their sum. It is a powerful tool. To use a Pivot Table
- Select an empty cell where we will make a Pivot Table. Go to Insert tab. Then select Pivot Table.
- A dialogue box Create PivotTable will appear. For the data to analyze select Select a table or range and select the range with a mouse just like Consolidation but with headers. This time in the box a new term for sheet name will also show up as pivot table can be used to get data from different worksheets too. Like in our example it is ‘3. Pivot Table’!$C$4:$D$17 for selecting cells C4 to D17 in 3. Pivot Table sheet.
- To input to a cell in the current worksheet select Existing Worksheet and in the location select a cell with the mouse or write ‘Worksheet Name’!Cell Id . Make sure you make the cell absolute. Like in our cell it is ‘3. Pivot Table’!$F$4 for inputting the value at Cell F4 in 3. Pivot Table worksheet. Then press OK.
- A Pivot Table is created.
- Click anywhere in the pivot table area and it would open the pivot table pane on the right. Drag to put the Customer field into the Rows area and Sum of Due into the Values area.
- Now we got the Sum of dues of all customers with their names in a Pivot Table.
In this article we have learned 3 ways to remove duplicate data and sum their values in excel. We hope you will find these methods intuitive and easy to follow. These types of problems are very common in many excel operations so we tried to help you to solve these problem with less effort. If you have any suggestions about how we could improve ourselves it will be great. Please provide feedback about what you liked in this article or where you think we could improve in the comment section. Make sure to rate this article, Thank you.