running totals

[k_babb]

i need some help on a formula to keep a count of how much each department is spending every month we have 4 department each has it own department id for it orders

i.e M for manufacturing
Q for Quality
ect
so column D has the department id, Then column e has the order cost how can i keep a running total on sheet 2 for how much each department has spent? so what i need is a formula that picks out all orders from column D that are for manufacturing (M) and then adds the totals from each order in column e and then paste that total on sheet 2 any help would be great

 

[MrKowz]

Welcome to the forums!

A Pivot Table would likely be the best, easiest, way to accomplish this. However, if you require a formula solution, try:

<b>Excel 2003</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>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Department ID</td><td style=";">Order Cost</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Q</td><td style="text-align: right;;">1137</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">M</td><td style="text-align: right;;">1105</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Q</td><td style="text-align: right;;">1059</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Q</td><td style="text-align: right;;">1137</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">M</td><td style="text-align: right;;">1296</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Q</td><td style="text-align: right;;">1091</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Q</td><td style="text-align: right;;">1285</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Q</td><td style="text-align: right;;">1328</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">M</td><td style="text-align: right;;">1304</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Q</td><td style="text-align: right;;">1017</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Q</td><td style="text-align: right;;">1287</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">M</td><td style="text-align: right;;">1261</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">M</td><td style="text-align: right;;">1338</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">M</td><td style="text-align: right;;">1290</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">M</td><td style="text-align: right;;">1122</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Q</td><td style="text-align: right;;">1061</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">M</td><td style="text-align: right;;">1256</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Q</td><td style="text-align: right;;">1180</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Q</td><td style="text-align: right;;">1033</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br />
<b>Excel 2003</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=";">Department</td><td style=";">Total Order Cost</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Q</td><td style="text-align: right;;">12615</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">M</td><td style="text-align: right;;">9972</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><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">B2</th><td style="text-align:left">=SUMIF(<font color="Blue">Sheet1!D:D,A2,Sheet1!E:E</font>)</td></tr></tbody></table></td></tr></table><br />

 

[T. Valko]

i need some help on a formula to keep a count of how much each department is spending every month we have 4 department each has it own department id for it orders

i.e M for manufacturing
Q for Quality
ect
so column D has the department id, Then column e has the order cost how can i keep a running total on sheet 2 for how much each department has spent? so what i need is a formula that picks out all orders from column D that are for manufacturing (M) and then adds the totals from each order in column e and then paste that total on sheet 2 any help would be great

See if this is what you had in mind.

Let's assume this is your data in the range Sheet1 A2:B10

<TABLE style="WIDTH: 82pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=108 border=0 x:str><COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1728" span=2 width=54><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2662975 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=54 height=17>Q</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>31 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Q</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">83 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>M</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">82 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>S</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">87 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Q</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">79 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>M</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">56 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>M</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">55 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>M</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">14 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>T</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">47 </TD></TR></TBODY></TABLE>

On Sheet2 cell A2 = some dept ID like Q.

Enter this formula on Sheet2 B2:

=SUMIF(Sheet1!A$2:A$10,A2,Sheet1!B$2:B$10)

 

[k_babb]

Thanks very much i will give it A try