Quantity from second worksheet

[Trevor Kaltenbrunn]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Inv Nu[/TD]
[TD]Date[/TD]
[TD]Item[/TD]
[TD]Qty[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]Labour[/TD]
[TD]4[/TD]
[TD]Labour[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Call Out[/TD]
[TD]1[/TD]
[TD]Call Out[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Pen[/TD]
[TD]7[/TD]
[TD]Pen[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Labour[/TD]
[TD]8[/TD]
[TD]Labour[/TD]
[/TR]
</tbody>[/TABLE]

Yearly Sales

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Labour[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Call Out[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[AlanY]

try

Book1
	A	B	C	D	E	F	G	H
1	Inv Nu	Date	Item	Qty	Description		Yearly Sales
2	1	2	Labour	4	Labour		Labour	12
3	2		Call Out	1	Call Out		Call Out	1
4	3		Pen	7	Pen		Pen	7
5	4		Labour	8	Labour
Sheet1

Cell Formulas
Range	Formula
H2	=SUMIF(C:C,G2,D:D)

 

[Trevor Kaltenbrunn]

try

	A	B	C	D	E	F	G	H
Inv Nu	Date	Item	Qty	Description	Yearly Sales
Labour	Labour	Labour
Call Out	Call Out	Call Out
Pen	Pen	Pen
Labour	Labour

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]12[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]7[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=SUMIF(C:C,G2,D:D)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Alan

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

 

[AlanY]

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

 

[Trevor Kaltenbrunn]

Thanks, i got it working.