Subtotaling based on account number

[fordpowr]

Hi all:

I have a sheet with two columns of data. One contains account numbers and the other contains values (dollars in this case). What I would like to do is create a function that sums the values for each given account code and the deletes the detail lines so only the subtotals are left. I tried using the subtotal function but it keep wanting to create its own subtotal descriptions. Note that the account numbers aren't sorted and appear multiple times throughout the file. For example:

Raw Data:

Acct# $
4001 5
4200 10
4001 3
4001 7


I would like the output to be (in order by account # and with the details lines deleted):

Acct# $
4001 15
4200 10


Any help would be greatly appreciated it! Thanks!

 

[crook_101]

Hi,

This seems to be a given for a pivot table - this will do everything that you describe above...

Are you OK using pivots??

If not, post back with the version of excel you are using

Ian

 

[jdmc45]

Assuming account numbers are in Column A and account $'s are in Column B, use the following for each account:

for example,

for 4001

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

etc for different account number

 

[crook_101]

Hi,

In addition to the above solution, I would copy the column of account numbers, assuming there are loads of them to a new column, as shown below, delete duplicates, then use the amended sumif copied down to save manuallly entering loads of individual formula...

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Acct#</td><td style="text-align: center;;">$</td><td style="text-align: center;;"></td><td style="text-align: center;;">Acc# no dupes</td><td style="text-align: center;;">$</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">4001</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;">4001</td><td style="text-align: center;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">4200</td><td style="text-align: center;;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;">4200</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">4001</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">4001</td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=SUMIF(<font color="Blue">A:A,D2,B:B</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=SUMIF(<font color="Blue">A:A,D3,B:B</font>)</td></tr></tbody></table></td></tr></table><br />

Ian

 

[fordpowr]

Thanks for all of your help, guys. The sumif function works perfectly for what I need!

 

[jdmc45]

No probs!