Sales summary

phantomrogue

New Member
Joined
Jun 18, 2015
Messages
2
Hi, was wondering if someone could please solve this problem for me? I have an Excel file with a worksheet called 'Data' detailing sales people and sales figures against each one.

Thomas 48000
Alice 12500
Bob 394
Thomas 1200
Alice 4000
Bob 30000

Now, in a separate sheet called 'Summary' I need to list total sales from all the sales people.

I.E.

Thomas 49200
Alice 16500
Bob 30394

This needs to dynamically update as sales are added to the 'Data' sheet. I am fairly new to Excel, so any help would be very much appreciated.

Thank you!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,338
Office Version
  1. 365
Platform
  1. Windows
This might help do a summation using criteria. It looks up the salemen's names in the Sales List and summarises each saleman's sales in the Sales Summary table.

Move the Sales Summary to any other worksheet you desire.

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #7B7B7B;;">Sales List</td><td style="text-align: right;background-color: #7B7B7B;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #E7E6E6;;">Salesman</td><td style="background-color: #E7E6E6;;">Sales</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Thomas</td><td style="text-align: right;;">48000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Alice</td><td style="text-align: right;;">12500</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Bob</td><td style="text-align: right;;">394</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Thomas</td><td style="text-align: right;;">1200</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Alice</td><td style="text-align: right;;">4000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Bob</td><td style="text-align: right;;">30000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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><td style="background-color: #7B7B7B;;">Sales Summary</td><td style="text-align: right;background-color: #7B7B7B;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #E7E6E6;;">Salesman</td><td style="background-color: #E7E6E6;;">Sales</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Alice</td><td style="text-align: right;background-color: #C6E0B4;;">16500</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Bob</td><td style="text-align: right;background-color: #C6E0B4;;">30394</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Thomas</td><td style="text-align: right;background-color: #C6E0B4;;">49200</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet45</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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">D12</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">C12=$A$3:$A$8</font>),$B$3:$B$8</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D13</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">C13=$A$3:$A$8</font>),$B$3:$B$8</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D14</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">C14=$A$3:$A$8</font>),$B$3:$B$8</font>)</td></tr></tbody></table></td></tr></table><br />
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,338
Office Version
  1. 365
Platform
  1. Windows
Super! I'm glad I helped.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,083
Messages
5,599,643
Members
414,326
Latest member
kfg1287

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top