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

Inv Nu	Date	Item	Qty	Description
1	2	Labour	4	Labour
2		Call Out	1	Call Out
3		Pen	7	Pen
4		Labour	8	Labour

<tbody>
</tbody>

Yearly Sales

Labour	?
Call Out	?
Pen	?
	?
	?

<tbody>
</tbody>

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
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

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

Sheet1​

Worksheet Formulas Cell Formula 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]

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.