Sum of Data

[djeerd]

I have an Excel spreadsheet with Job Name in Column A and Material cost in column B. There may be many entries for the same job number in column A. How can I get Excel to show the Job Numbers with the SUM of the Material for each job number?


This is my Excel sheet (sorry, not sure how to show it anyother way).

Column A Column B
A 100
B 50
B 25
B 10
A 150
C 20
C 10
A 125



I need Excel to add up data sums in column B.

A 375
B 85
C 30

 

[crook_101]

Hi,

SUMIF will work as shown below:

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">A</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">B</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">B</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">B</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">A</td><td style="text-align: right;;">150</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">c</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">c</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">A</td><td style="text-align: right;;">125</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">A</td><td style="text-align: right;;">375</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">B</td><td style="text-align: right;;">85</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">C</td><td style="text-align: right;;">30</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">B11</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$1:$A$8,"A",$B$1:$B$8</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B12</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$1:$A$8,"B",$B$1:$B$8</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B13</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$1:$A$8,"C",$B$1:$B$8</font>)</td></tr></tbody></table></td></tr></table><br />


Cheers,
Ian

 

[jmthompson]

I think your best bet will be subtotals. Sort by column A. Subtotal, at each change in A, sum B.

This way you don't have to create a separate formula for each job name.

 

[djeerd]

Yes I see.

I used A, B & C as easy examples. What could I do if I did not want to create a formula for each job number? The list may be 100 jobs and I would not want create a formula for every job.

Could Excel search the list and create the formula itself?

 

[crook_101]

Hi,

Probably the easiest way would be to copy your list of job numbers (column A) to a new sheet, filter it to remove duplicates. Once you have your list of job numbers you can paste them whereever you want and use the formula ammend as shown below, the ammended formula will pick up the new job number and sum all of the totals as previously:

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">A</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">B</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">B</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">B</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">A</td><td style="text-align: right;;">150</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">c</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">c</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">A</td><td style="text-align: right;;">125</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">A</td><td style="text-align: right;;">375</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">B</td><td style="text-align: right;;">85</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">C</td><td style="text-align: right;;">30</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">B11</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$1:$A$8,A11,$B$1:$B$8</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B12</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$1:$A$8,A12,$B$1:$B$8</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B13</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$1:$A$8,A13,$B$1:$B$8</font>)</td></tr></tbody></table></td></tr></table><br />

Ian

 

[djeerd]

Thanks for your responses. I still am having a hard time with this.

Here is actual data I am working with. First Column is JOb Number, second column is Cost.

The actuall list is 500 lines and there may be multiple "Job Numbers" (this list is small and may not have any duplicates). It would be imposible for me to create a formula by manually searching for unique Job nUmber (First Column).




1030228 40.92
1030255 207.49
1130274 430.82
1030193 347.95
1130275 528.04
1030203 15.06
1130276 598.00
1030271 242.27
1030238 150.78
1130277 289.31
1030228 48.36
1030246 60.09
1030193 487.83
1130278 273.77
1030246 489.52
1030253 9.90
1130279 79.50
1030044 329.05
1130280 176.67
1030258 52.21
1030247 516.12
1130279 20.82
1030246 113.77
1011019 10.00
1130281 26.50
1130282 1,355.76
1130283 8.55
1130276 63.24
1130284 55.52
1130285 26.00
1130286 22.00
1011017 37.42
1030170 662.90

 

[djeerd]

My entire list has 438 duplicates and 178 uniqye numbers.

 

[kevin21]

I assume your numbers that may have duplicates is in column "A" and the other is column "B". Sort your numbers ascending in column a first then run this code below. It will pull each unique number out and put it in sheet 2 (be sure your workbook has a sheet 2).

c = 1
r = 1
For j = 1 To 2500
If Sheets("Sheet1").Cells(j, 1).Value = c Then

Else
Sheets("Sheet2").Cells(r, 2).Value = Sheets("sheet1").Cells(j, 1).Value
c = Sheets("sheet1").Cells(j, 1).Value
r = r + 1
End If
Next j

This will pull each unique number out and place it in sheet 2. Then use the sum if below beside each on of the unique numbers (B column).

=SUMIF('sheet1'!$A$1:$A$2000,A1,'sheet1'!$B$1:$B$2000)

Just drag this down.