lookup and sum

[BT690]

Hi there

Simple task which has been bugging me for days. Keep coming back to it with a possible solution but it fails.

Two sheets of data
Sheet 1 has inventory
Sheet 2 has product
I need to update the sheet 2 product with the sheet 1 inventory as per example below

Have tried xlookup, sumif, index, match but cant find the solution. next step is to spend a day writing it down on paper and doing it manual then input results into a sheet.

Many thanks in advance.

Sheet 1				Sheet 2
ID	Product	Qty		ID	Apples	Banana	Oranges	Pears	Total
100	Apples	6		100
101	Oranges	4		101
101	Pears	5		102
103	Apples	1		103
103	Pears	5		104
103	Oranges	2		105
105	Oranges	4		106
106	Apples	8		107
107	Oranges	2		108
107	Oranges	6		109
107	Apples	4		110
109	Pears	4		111
109	Oranges	6		112
110	Pears	7		113
111	Pears	4		114
112	Apples	2		115
113	Apples	8		116
113	Pears	1		117
113	Oranges	9		118
115	Banana	5		119
115	Oranges	2		120
116	Apples	7
116	Banana	2
117	Apples	5
119	Apples	2

 

[Toadstool]

Hi BT690,

If this is the first few rows of Sheet1

Book1
	A	B	C
1	ID	Product	Qty
2	100	Apples	6
3	101	Oranges	4
4	101	Pears	5
5	103	Apples	1
6	103	Pears	5
7	103	Oranges	2
Sheet1

...then does this work on Sheet2?

Book1
	A	B	C	D	E	F
1	ID	Apples	Banana	Oranges	Pears	Total
2	100	6	0	0	0	0
3	101	0	0	4	5	0
4	102	0	0	0	0	0
5	103	1	0	2	5	0
6	104	0	0	0	0	0
7	105	0	0	4	0	0
8	106	8	0	0	0	0
9	107	4	0	8	0	0
10	108	0	0	0	0	0
11	109	0	0	6	4	0
12	110	0	0	0	7	0
13	111	0	0	0	4	0
14	112	2	0	0	0	0
15	113	8	0	9	1	0
16	114	0	0	0	0	0
17	115	0	5	2	0	0
18	116	7	2	0	0	0
19	117	5	0	0	0	0
20	118	0	0	0	0	0
21	119	2	0	0	0	0
22	120	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
B2:F22	B2	=SUMIFS(Sheet1!$C:$C,Sheet1!$B:$B,B$1,Sheet1!$A:$A,$A2)

 

[BT690]

Hi Toadstool, yes that works thanks. One of my attempts must have been so close as that was my intent. Maybe I didn't have the absolute reference quite right??

Cheers
BT