combine rows and sum data with the same id
 

Hi,
Here is an example of the data I have.
Account ID Amount
101567 $505.00
101567 $875.00
101567 $2,450.50
253570 $1,178.90
253570 $875.00
346677 $1,928.33
I have thousands of accounts with multiple amounts having the same ID and I
need to add these amounts and combine them so that there is only one row per
account ID. Please help...

Answer: combine rows and sum data with the same id
 

Sure, I can help you with that. Here's how you can combine rows and sum data with the same ID in Microsoft Excel:

1. First, select the entire data range that you want to work with, including the column headers.
2. Next, go to the Data tab in the ribbon and click on Remove Duplicates. In the pop-up window, make sure that only the Account ID column is selected and click OK. This will remove all duplicate rows based on the Account ID column.
3. Now, you need to sum up the amounts for each unique Account ID. To do this, you can use the SUMIF function. In a new column next to the Amount column, enter the formula
   Formula:

   ---

   "=SUMIF(A:A,A2,B:B)" 


   ---

   (assuming that the Account ID column is column A and the Amount column is column B). This formula will sum up all the amounts in column B that have the same Account ID as the current row.
4. Copy the formula down to all the rows in the new column. You should now have a new column with the total amount for each unique Account ID.
5. Finally, you can delete the original Amount column if you no longer need it.

That's it! You should now have a table with one row per unique Account ID and the total amount for each account. Let me know if you have any questions or if there's anything else I can help you with.

combine rows and sum data with the same id
 

One good, quick way - Use a pivot. It'll deliver exactly what you seek, in a
matter of seconds, and despite the voluminous data

Create a pivot (click DataPivotTable), then in step 3 of the wizard, click
Layout, drag n drop "Account ID" into ROW area, "Amount" into DATA area
(it'll show as Sum of Amount). Click OK Finish. That's it. Hop over to the
pivot sheet for the amazing results delivered: a listing of all the unique
Account IDs in a column with their corresponding sums next to it

Wave your success here, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"tenny" wrote:
Here is an example of the data I have.
Account ID Amount
101567 $505.00
101567 $875.00
101567 $2,450.50
253570 $1,178.90
253570 $875.00
346677 $1,928.33
I have thousands of accounts with multiple amounts having the same ID and I
need to add these amounts and combine them so that there is only one row per
account ID. Please help...

combine rows and sum data with the same id
 

hi Tenny,

I suggest using a Pivot Table (PT) to summarise your data (this is just
of many possible solutions). Here is Jon Peltier's index page which has
links to a lot of useful PT reading:
'Using Pivot Tables and Pivot Charts in Microsoft Excel'
(http://peltiertech.com/Excel/Pivots/pivotstart.htm)
including this initial explanation of what Pivot tables do:
'Using Pivot Tables in Microsoft Excel'
(http://peltiertech.com/Excel/Pivots/pivottables.htm)

hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112303

combine rows and sum data with the same id
 

Hi Tenny

I'd use Data Consolidation. If your data is arranged as you showed, then
just click in the cell where you want your data to start-I would place it on
another worksheet. Go to DataConsolidate and choose Sum in the "Function:"
box. Then click the minimize button in the "Reference:" area. Highlite the
account numbers and data in your spreadsheet, then click back into the
Consolidate box. Click the Add button. In the "Use labels in" box, click
Left column, then click OK. If done correctly, you should have 1 row for
each unique account # and the corresponding summed amounts on the new
worksheet.

Please let me know if it works...
--
OneFineDay


"tenny" wrote:

Hi,
Here is an example of the data I have.
Account ID Amount
101567 $505.00
101567 $875.00
101567 $2,450.50
253570 $1,178.90
253570 $875.00
346677 $1,928.33
I have thousands of accounts with multiple amounts having the same ID and I
need to add these amounts and combine them so that there is only one row per
account ID. Please help...