Sum, Index and Match

[Goddess]

Hi,

I'm at loss with Sum, Index & Match again. Can't seem to get it right all the time

I have this data and need to sum them based on the criteria given. For example, I need to sum Apple - Domestic in Group A & B. It's a long list, so will need a formula to sum them up automatically.

Any help is appreciated. Thanks!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		Group A												Group B
2		Apple	Apple	Total	Grape	Grape	Total	Orange	Orange	Total	Cherry	Cherry	Total	Apple	Apple	Total	Grape	Grape	Total	Orange	Orange	Total	Cherry	Cherry	Total
3	Entities	Domestic	Overseas	Apple	Domestic	Overseas	Grape	Domestic	Overseas	Orange	Domestic	Overseas	Cherry	Domestic	Overseas	Apple	Domestic	Overseas	Grape	Domestic	Overseas	Orange	Domestic	Overseas	Cherry
4	Co A	5		5		4	4		6	6		3	3		1	1			0	1		1	1	8	9
5	Co B		5	5		1	1			0			0	8		8			0		3	3			0
6	Co C			0	3		3		1	1			0			0	7	2	9			0	5		5
7	Co D	2		2			0			0	2		2			0			0			0			0
8
9
10			TOTAL
11	Apple	Domestic	15
12		Overseas
13	Grape	Domestic
14		Overseas
15	Orange	Domestic
16		Overseas
17	Cherry	Domestic
18		Overseas
19
Sheet1

Cell Formulas
Range	Formula
D3,Y3,V3,S3,P3,M3,J3,G3	D3	=IF(ISBLANK(B2),"",B2)
Y4:Y7,V4:V7,S4:S7,P4:P7,M4:M7,J4:J7,G4:G7,D4:D7	D4	=SUM(B4:C4)
C11	C11	=SUM(B4:B7)+SUM(N4:N7)

 

[Peter_SSs]

Sorry, I didn't use any of the 3 functions that you mentioned.

22 07 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		Group A												Group B
2		Apple	Apple	Total	Grape	Grape	Total	Orange	Orange	Total	Cherry	Cherry	Total	Apple	Apple	Total	Grape	Grape	Total	Orange	Orange	Total	Cherry	Cherry	Total
3	Entities	Domestic	Overseas	Apple	Domestic	Overseas	Grape	Domestic	Overseas	Orange	Domestic	Overseas	Cherry	Domestic	Overseas	Apple	Domestic	Overseas	Grape	Domestic	Overseas	Orange	Domestic	Overseas	Cherry
4	Co A	5		5		4	4		6	6		3	3		1	1			0	1		1	1	8	9
5	Co B		5	5		1	1			0			0	8		8			0		3	3			0
6	Co C			0	3		3		1	1			0			0	7	2	9			0	5		5
7	Co D	2		2			0			0	2		2			0			0			0			0
8
9
10			TOTAL
11	Apple	Domestic	15
12		Overseas	6
13	Grape	Domestic	10
14		Overseas	7
15	Orange	Domestic	1
16		Overseas	10
17	Cherry	Domestic	8
18		Overseas	11
Sum

Cell Formulas
Range	Formula
C11:C18	C11	=SUMPRODUCT((B$2:Y$2=LOOKUP("zz",A$11:A11))*(B$3:Y$3=B11)*B$4:Y$7)

 

[Goddess]

Sorry, I didn't use any of the 3 functions that you mentioned.

22 07 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1		Group A												Group B
2		Apple	Apple	Total	Grape	Grape	Total	Orange	Orange	Total	Cherry	Cherry	Total	Apple	Apple	Total	Grape	Grape	Total	Orange	Orange	Total	Cherry	Cherry	Total
3	Entities	Domestic	Overseas	Apple	Domestic	Overseas	Grape	Domestic	Overseas	Orange	Domestic	Overseas	Cherry	Domestic	Overseas	Apple	Domestic	Overseas	Grape	Domestic	Overseas	Orange	Domestic	Overseas	Cherry
4	Co A	5		5		4	4		6	6		3	3		1	1			0	1		1	1	8	9
5	Co B		5	5		1	1			0			0	8		8			0		3	3			0
6	Co C			0	3		3		1	1			0			0	7	2	9			0	5		5
7	Co D	2		2			0			0	2		2			0			0			0			0
8
9
10			TOTAL
11	Apple	Domestic	15
12		Overseas	6
13	Grape	Domestic	10
14		Overseas	7
15	Orange	Domestic	1
16		Overseas	10
17	Cherry	Domestic	8
18		Overseas	11
Sum

Cell Formulas
Range	Formula
C11:C18	C11	=SUMPRODUCT((B$2:Y$2=LOOKUP("zz",A$11:A11))*(B$3:Y$3=B11)*B$4:Y$7)

Thank you! As long as it works, any formula is fine

 

[Peter_SSs]

Thank you! As long as it works, any formula is fine

I assumed that was the case.
You're welcome. Thanks for the follow-up.