Sum certain cells from column 2 based on category value in column 1

[up&down]

I'm using Excel 2007. I'm far from being a pro.

I need to get a sum of only the cells in column "B" that have the same common value in column "A".

The number of cells per category (column "A") is not constant and is between 1 and 20. Also, the number of categorical values in column "A" is over 2000.

An example of the data to start is below.

159 6
159 6
159 9
159 3
159 1
159 4
159 3
160 1
160 5
160 1
160 2
160 6
160 4
160 7
160 4
160 5
160 5
160 9
160 8
161 1
161 3
161 1
161 5
161 2
161 2
161 6
162 2
163 8
164 8
164 4

I need the above to look like the below.

159 6 32
159 6
159 9
159 3
159 1
159 4
159 3
160 1 57
160 5
160 1
160 2
160 6
160 4
160 7
160 4
160 5
160 5
160 9
160 8
161 1 20
161 3
161 1
161 5
161 2
161 2
161 6
162 2 2
163 8 8
164 8 12
164 4

Thanks in advance. Please let me know what other info you need.

[benishiryo]

hi there, welcome to the forum. assuming data starts in A2, then:
=IF(A2<>A1,SUMIF($A$2:$A$31,A2,$B$2:$B$31),"")

if it doesnt work, uploading a sample Excel file in the thread will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
ideally, it should contain your desired results

[up&down]

Wow benishiryo. That was fast and it looks like it totally works. I was expecting something complicated like a macro or something. Thank you so much. If you have the time please keep an eye on this thread. I want to pick the function you gave me apart so that I understand it in full. Thank you again.

[up&down]

benishiryo,

I'm having trouble understanding the code. Could you explain each bit of syntax for me? Thank you.

[benishiryo]

you are only summing up the data when the current row in Column A is not equals to the previous row. for eg. A2 not equals to A1 (159 not equals to blank). so i started the formula with:
=IF(A2<>A1,Sum Up the values in column B where column A equals to A2,"")

for the red portion, i used SUMIF & it basically means that. the SUMIF has 3 arguments.
range
this is the place where the criteria must be found. -> $A$2:$A$31

criteria
my criteria is to find 159 in the range -> A2 (where the value is 159)

sum_range
the values to sum up when the criteria is found. do note that the number of rows here must be the same in range.

[up&down]

Thank you. That is clear.