Return Value Using SUMIF and OFFSET Function

[overthinker519]

Hi all,

I have a set of data source with size and quantities input horizontally. I try to return those values in G12:G17 based on criterias "HELPER" and "Size". The best way I know is to utlize functions SUMIF and OFFSET. However the size sets from data source may differ for different products. I am hoping someone could show me the way through this task. I'm open for any soluction as long as it is not VBA.

Workbook 1.xlsx
	A	B	C	D	E	F	G	H	I
1	HELPER	POS/Account	Style #	Color Description	Code	S	M	L	XL
2	Taipei1904762WHIT	Taipei	1904762	WHITE	WHIT	2	4	3
3	Taichung1904763BLAC	Taichung	1904763	BLACK	BLAC		1	1	1
4	HELPER	POS/Account	Style #	Color Description	Code	M	L	XL	XXL
5	Keelung1904764NATL	Keelung	1904764	NATURAL	NATL		3	5	4
6	Kaohsiung1904765BLAC	Kaohsiung	1904765	BLACK	BLAC	1	1	1
7
8
9	HELPER	POS/Account	Style #	Color Description	Code	Size	Qty
10	Taipei1904762WHIT	Taipei	1904762	WHITE	WHIT	S	2
11	Taipei1904762WHIT	Taipei	1904762	WHITE	WHIT	M	4
12	Taipei1904762WHIT	Taipei	1904762	WHITE	WHIT	L	3
13	Kaohsiung1904765BLAC	Kaohsiung	1904765	BLACK	BLAC	M	2
14	Kaohsiung1904765BLAC	Kaohsiung	1904765	BLACK	BLAC	L	0
15	Kaohsiung1904765BLAC	Kaohsiung	1904765	BLACK	BLAC	XL	0
Sheet1

Cell Formulas
Range	Formula
A2:A3,A10:A15,A5:A6	A2	=B2&C2&E2
G10:G15	G10	=SUMIF($A$2:$A$6,A10,OFFSET($A$1,MATCH($A10,$A$2:$A$6,0),MATCH($F10,$A$1:$I$1,0)-1,7,1))

Thank you for your time.

 

[KRice]

Here is one idea that doesn't require concatenating the columns:

Book2
	A	B	C	D	E	F	G	H	I
1	HELPER	POS/Account	Style #	Color Description	Code	S	M	L	XL
2	Taipei1904762WHIT	Taipei	1904762	WHITE	WHIT	2	4	3
3	Taipei1904762WHIT	Taipei	1904762	WHITE	WHIT	3	5	4	2
4	Taichung1904763BLAC	Taichung	1904763	BLACK	BLAC		1	1	1
5	HELPER	POS/Account	Style #	Color Description	Code	M	L	XL	XXL
6	Keelung1904764NATL	Keelung	1904764	NATURAL	NATL		3	5	4
7	Kaohsiung1904765BLAC	Kaohsiung	1904765	BLACK	BLAC	1	1	1
8
9
10	HELPER	POS/Account	Style #	Color Description	Code	Size	Qty
11		Taipei	1904762	WHITE	WHIT	S	5
12		Taipei	1904762	WHITE	WHIT	M	9
13		Taipei	1904762	WHITE	WHIT	L	7
14		Taipei	1904762	WHITE	WHIT	XL	2
15		Taichung	1904763		BLAC	XL	1
16		Kaohsiung	1904765	BLACK	BLAC	M	1
17		Kaohsiung	1904765	BLACK	BLAC	L	1
18		Kaohsiung	1904765	BLACK	BLAC	XL	0
Sheet1

Cell Formulas
Range	Formula
A2:A4,A6:A7	A2	=B2&C2&E2
G11:G18	G11	=IFERROR(SUM(FILTER(FILTER($F$2:$I$7,($B$2:$B$7=B11)*($C$2:$C$7=C11)*($E$2:$E$7=E11)),$F$1:$I$1=F11)),"")