Complex match / lookup calculation, is this possible?

[eduzs]

I have a sheet like that:

	FOOD	WATER	TOOLS	FOOD	BOOKS	BOOKS	WATER
CL1	10			15
CL3
CL1	30			45
CL2

There's repetition in columns A rows, and in columns B-H. row 1.
I need to consolidate thist something like that:

	FOOD	WATER	TOOLS	BOOKS
CL1	formula? =10+15+30+45=100
CL2
CL3

Which formula can I use to totalize in the table above??
* Columns and rows are not fixed position, may repeat n times (lookup? match? sumifs?)
The result will consolidate the values from the 1st table in the second table, that does not have repetition of FOOD/WATER/TOOLS/BOOKS or CL1...CL3
Thanks

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E	F	G	H
1		FOOD	WATER	TOOLS	FOOD	BOOKS	BOOKS	WATER
2	CL1	10			15
3	CL3					12	12
4	CL1	30			45
5	CL2		10					5
6
7		FOOD	WATER	TOOLS	BOOKS
8	CL1	100	0	0	0
9	CL2	0	15	0	0
10	CL3	0	0	0	24
Sheet3

Cell Formulas
Range	Formula
B8:E10	B8	=SUMPRODUCT(($A$2:$A$5=$A8)*($B$1:$H$1=B$7)*$B$2:$H$5)

 

[eduzs]

Superb! Works fine! Thanks!

 

[AhoyNC]

You're welcome. Thanks for the feedback.