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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,177
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,177
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,109,163
Messages
5,527,163
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top