Multiple look up

[Johnboy28]

Hi, Does anyone know what formula I can use to sum up the Qty based on code and area. Note - Qty can only include these area (CS, C1 & CC7) - Exclude all others.

Data Table
Code	Product	Area	Qty
24680	Sample 1	CS	2
24931	Sample 2	CS	47
24970	Sample 3	C1	144
24680	Sample 1	CC1	24
24902	Sample 5	CC1	37
26562	Sample 6	MP	0
26562	Sample 7	CC7	0
Summary
CODE	PRODUCT	QTY
24680	Sample 1	??
24931	Sample 2	??
24970	Sample 3	??
24902	Sample 5
26562	Sample 6
26562	Sample 6
Area (Only include)
CS
C1
CC7

 

[arthurbr]

Have you tried a Pivot Table?

 

[Johnboy28]

I thought of using a pivot - The raw data is not in the best layout.

 

[arthurbr]

Just filter out the unneeded Areas ( use Tabular layout, it's easier )

 

[Darren Bartrup]

Try SUMIFS.

 

[Johnboy28]

Just filter out the unneeded Areas ( use Tabular layout, it's easier )

Thanks for the reply. Hopefully the below makes more sense.. I was hoping to use a formula to match Product code & area (Only the 3 selected area) ignoring the rest to sum up Qty. I have included the answer for the 1st 3. Thank you

Data Table
Product Code	Area	Qty
24680	CS	2
24931	CS	47
24970	C1	144
24680	CC1	24
24902	CC1	37
26562	MP	55
26562	CS	88
99888	MP	45
Summary
Product CODE	QTY		Area (Only these areas to be included in Summary)
24680	2		CS
99888	0		C1
24931	47		CC7
24902	??
26562	??
24902	??

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	Code	Product	Area	Qty			CODE	PRODUCT	QTY
2	24680	Sample 1	CS	2			24680	Sample 1	2
3	24931	Sample 2	CS	47			24931	Sample 2	47
4	24970	Sample 3	C1	144			24970	Sample 3	144
5	24680	Sample 1	CC1	24			24902	Sample 5	0
6	24902	Sample 5	CC1	37			26562	Sample 6	2
7	26562	Sample 6	MP	11
8	26562	Sample 7	CC7	2
9
Main

Cell Formulas
Range	Formula
I2:I6	I2	=SUM(SUMIFS(D:D,A:A,G2,C:C,{"CS","C1","CC7"}))

 

[Johnboy28]

=SUM(SUMIFS(D:D,A:A,G2,C:C,{"CS","C1","CC7"}))

Thank you. That works for what I need. Thanks again for your help.

 

[Fluff]

Glad we could help & thanks for the feedback.