Excel help please!

[Shadowmaster02]

I have a spreadsheet that has a list of location numbers in one column and then an amount in the next column. The tricky part is that the column with the location numbers has several entries for each location. I would like to sum the total amount for each location and put it in a new column.

Here is an example:

Col 1 Col 2
1234 10
1234 15
1238 25
1238 22
1238 8
1299 15

I would like to take all the rows where column 1 = 1238 and sum the values that are found in column 2. So it would be 25 + 22 + 8. I would like to do it for every different location value.

There are approx 500 different locations and more than 1200 rows in this spreadsheet, so it would be extremely tedious to do it manually.

Thanks in advance!

 

[DannyLove]

Why don't you use a basic database like Access instead of an excel sheet.
If your locations are one of a kind, covert those into unique keys, poof, query it, poof problem solved

 

[Traire]

or, in the first cell of column 3 use the following

=SUMIF(A:A,A1,B:B)

Then autofill down through all of the third column, so the second cell down in colum three would look like:

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

then

=SUMIF(A:A,A3,B:B)
=SUMIF(A:A,A4,B:B)
=SUMIF(A:A,A5,B:B)

etc...

Its not the most elegant, or the best way to do it, but it will give you what you need. The "right" way to do this in Excel would be to use a Pivot table. If you want to do it that way:

1. Pick the spot where you want your pivot table to be by clicking in a cell
2. Click the "Data" menu and choose "Pivot Table and Pivot Chart Report"
3. Choose "Microsoft Excel list or Database" and choose "Pivot Table" and hit the next button
4. In the next window Highlight the data you want in the pivot chart, should say something like "Sheet1!$A:$B" then click the next button
5. On this menu, click the "Layout" button.
6. The next screen should show you the pivot table layout in the center. On the right side should be two boxes with the data from the first row in your sheet. Click the top box and drag it into the part of the pivot table called "Row". Then click the second box and drag it into the middle part of the pivot table called "Data". The box in the data field should say "Count of XXX" or something similar. We want to get the sum, so double click on it and choose "sum" and click ok. Click ok again on the layout screen. And click "Finish" on the last screen.

Your done.

 

[Concillian]

I was able to do it without a macro, but it's kind of a kludge to do so.

Assuming 1234 is in A2 and 10 is in B2:
C2 and below has the formula:
=IF(C1="",IF(A1=A2,SUM(B1:B2),""),IF(A1=A2,B2,""))

Then column D make this formula:
=IF(C2="","",IF(C3="",SUM(C1:C2),IF(C1="","",C1)))

Then copy the formula from Column D into Columns E, F, G etc... until you end up with only one number for each column 1 match.

You probably want the associated location value for the sum, so then once you get them all down to only one number, then you would put the folloing formula in the next column:
=IF(<prevcol>2="","",A2)

replacing <prevcol> with the appropriate letter

Then you can highlight both columns and choose edit --> Goto --> Special ... then choose formulas and check numbers but no other checkboxes, then copy and paste somewhere else to get rid of all the spaces in between the numbers.

Like I said, a kludge, but it works. At least in the example I did.


Edit: Looks like Traire is more an Excel master than I

 

[Shadowmaster02]

Thanks Traire, that worked for what I needed to do.