# running totals

#### k_babb

##### Board Regular
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### MrKowz

##### Well-known Member
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 />

#### T. Valko

##### Well-known Member
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

##### Board Regular
Thanks very much i will give it A try

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,277
Messages
5,836,343
Members
430,421
Latest member
Natas

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back