Quantity from second worksheet

Trevor Kaltenbrunn

New Member
Joined
Feb 2, 2019
Messages
9
Hi Please help,

I have a worksheet entitled Sales and i have a second Sheet for a summary of sales YEARLY SALES by item. Sales worksheet SALES has 8 columns ( Invoice,date,item, Quantity, Description,Ect). In the second worksheet i would like to check a certain item in Sales worksheet and see how many have been sold.

In Sales worksheet column 3 has item and column 4 has amount soled. If i now have multiple row entries of the same item i need the value of that items total sold amount to be displayed in the summary sheet, next to the item header.

If you see example below, in SALES Column 3 i have labour Twice, i need to check the total of the labour sold in Column 2 YEARLY SALES

Sales Workbook

Inv NuDateItemQtyDescription
12Labour4Labour
2Call Out1Call Out
3Pen7Pen
4Labour8Labour

<tbody>
</tbody>

Yearly Sales

Labour?
Call Out?
Pen?
?
?

<tbody>
</tbody>

Thanks
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Inv Nu</td><td style=";">Date</td><td style=";">Item</td><td style=";">Qty</td><td style=";">Description</td><td style="text-align: right;;"></td><td style=";">Yearly Sales</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style=";">Labour</td><td style="text-align: right;;">4</td><td style=";">Labour</td><td style="text-align: right;;"></td><td style=";">Labour</td><td style="text-align: right;background-color: #E2EFDA;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style=";">Call Out</td><td style="text-align: right;;">1</td><td style=";">Call Out</td><td style="text-align: right;;"></td><td style=";">Call Out</td><td style="text-align: right;background-color: #E2EFDA;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style=";">Pen</td><td style="text-align: right;;">7</td><td style=";">Pen</td><td style="text-align: right;;"></td><td style=";">Pen</td><td style="text-align: right;background-color: #E2EFDA;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style=";">Labour</td><td style="text-align: right;;">8</td><td style=";">Labour</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">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: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=SUMIF(<font color="Blue">C:C,G2,D:D</font>)</td></tr></tbody></table></td></tr></table><br />
 

Trevor Kaltenbrunn

New Member
Joined
Feb 2, 2019
Messages
9
try

ABCDEFGH
1Inv NuDateItemQtyDescriptionYearly Sales
212Labour4LabourLabour12
32Call Out1Call OutCall Out1
43Pen7PenPen7
54Labour8Labour

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
H2=SUMIF(C:C,G2,D:D)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Hi Alan

What can i do if they are on seperate worksheets. I want the results your column H in a seperate worksheet.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
the easiest way is create them in the same sheet, cut and paste the columns (e.g. G & H) to the new sheet, excel will adjust the reference automatically
 

Watch MrExcel Video

Forum statistics

Threads
1,128,064
Messages
5,628,391
Members
416,315
Latest member
certainlyfrustrated

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