Sum column B if Column A is the same?

[natural1]

A B
10 135
10 19
10 15
10 16
11 27
11 19
11 230
11 152
11 37
12 56
12 99​

What I am trying to do is add all the numbers in column B if the numbers in column A are the same. For example A1 to A4 are the same so I want to add B1to B4. A5 to A9 are the same so I want to add B5 to B9, etc. My first time to this forum, so I hope I explained this properly. Please let me know if you need more information. Thank you​

 

[jbeaucaire]

Like so, enter the values you want to "sum by" and then the formula shown in the adjacent cell:

Excel Workbook
	A	B	C	D	E
1	IDs	Values		ID	Total
2	10	135		10	185
3	10	19		11	465
4	10	15		12	155
5	10	16
6	11	27
7	11	19
8	11	230
9	11	152
10	11	37
11	12	56
12	12	99
Sheet4

 

[natural1]

Thanks for the quick response. I need to explain further, my fault. The numbers in column A will always be changing and my spreadsheets will very from about 50 rows to 300 rows. I need a formula to figure out what cells or values to sum by and a formula to sum Column B accordingly.

 

[jbeaucaire]

Try this. The formula in D2 is different from the one in D3. The D3 formula is an ARRAY formula, you must press CTRL-SHIFT-ENTER to confirm it, then copy the D3 formula down as far as you'd like, it will show 0 when it runs out of values.

Copy the E2 formula down the column E.

You can suppress the zeros by clicking on Tools > Options > View > [ ] Zero Values and uncheck that option for this sheet.

Excel Workbook
	A	B	C	D	E
1	IDs	Values		IDs	Total
2	10	135		10	185
3	10	19		11	465
4	10	15		12	155
5	10	16		0	0
6	11	27		0	0
7	11	19		0	0
8	11	230		0	0
9	11	152		0	0
10	11	37		0	0
11	12	56		0	0
12	12	99		0	0
Sheet4

 

[Peter_SSs]

natural1

Welcome to the MrExcel board!

Excel has a couple of built-in features that relate to what you seem to want. Post back if either of these seems like it might be of use and you need more help.


1. Subtotals (Found in the Data menu/ribbon)

Excel Workbook
	A	B
1	IDs	Values
2	10	135
3	10	19
4	10	15
5	10	16
6	10 Total	185
7	11	27
8	11	19
9	11	230
10	11	152
11	11	37
12	11 Total	465
13	12	56
14	12	99
15	12 Total	155
16	Grand Total	805
Subtotals

2. PivotTable (found in Data menu pre 2007 or Insert ribbon 2007)

Excel Workbook
	A	B	C	D	E	F
1	IDs	Values		Sum of Values
2	10	135		IDs	Total
3	10	19		10	185
4	10	15		11	465
5	10	16		12	155
6	11	27		Grand Total	805
7	11	19
8	11	230
9	11	152
10	11	37
11	12	56
12	12	99
13
PivotTable

 

[natural1]

Mr. Rocket Scientist, Thank you so very much. The formulas work just fine. I definately took a wrong turn when I was working on this. I will have to go through your formula a bit to understand how it all works. Thanks again for your quick response and solutions.

 

[jbeaucaire]

Glad to help.

Peter is right about the pivot tables being cool and worth looking into. I don't use them as much as I should, mostly cause of the extra hoops you have to go through to get them to update each time the data updates. As I said, I don't use them a lot, perhaps there is an easy way to get that, too.

 

[natural1]

Peter, I just tried PivotTable and Subtotals for the first time (ever). They both work fine. I think Pivot table works best of the two for my current needs. I now have 3 possible ways of doing this, so I will play around with them all and see what happens. Thank you both, jbeaucaire and Peter, for your help and quick responses.