Sumif if other criteria is in another table

[ExcelNewbie2020]

Sir,
I need to find the total percentage from the percent table in which other criteria is parallel but is another table.. please see table below

sample.xlsx
	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	Nour Chen	Theresa Prentice	Montana Grimes	Piotr Vaughn	Myah Ballard	Keanan Espinosa		Armenia	Nigeria	India	USA	Honduras	Togo	Georgia	Mauritania	Trinidad and Tobago			EXPECTED RESULT		%
2	18%	1%	5%	3%	6%	1%		A	E	B	D	E	E	C	E	C					A	B	C	D	E
3	13%	1%	6%	2%	2%	1%		B	A	C	E	A	C	D	C	A			Armenia	Nour Chen	26%	25%	19%	17%	14%
4	5%	1%	8%	1%	4%	18%		C	B	D	C	B	D	E	D	B			Nigeria	Theresa Prentice
5	6%	1%	7%	1%	5%	13%		D	C	E	D	C	E	A	E	C			India	Montana Grimes
6	8%	5%	6%	18%	6%	1%		E	D	A	E	D	A	B	A	D			USA	Piotr Vaughn
7	7%	3%	5%	13%	8%	1%		A	E	B	A	E	B	C	B	E			Honduras	Myah Ballard
8	6%	6%	4%	1%	7%	5%		B	C	B	C	C	B	D	B	A			Togo	Keanan Espinosa
9	5%	2%	3%	1%	6%	3%		C	E	C	D	D	C	E	C	B			Georgia	Montana Grimes
10	4%	4%	2%	5%	5%	6%		D	C	C	E	E	C	C	D	C			Mauritania	Piotr Vaughn
11	3%	18%	1%	3%	4%	2%		E	D	D	C	C	D	D	E	D			Trinidad and Tobago	Myah Ballard
12	2%	13%	1%	6%	1%	4%		C	E	E	C	E	E	D	C	E
13	1%	5%	18%	2%	18%	5%		D	A	D	A	C	A	E	D	C
14	1%	6%	13%	4%	13%	6%		E	B	E	B	D	B	A	C	D
15	1%	8%	1%	5%	1%	8%		A	B	A	C	E	C	B	A	E
16	1%	7%	1%	6%	1%	7%		B	C	B	D	A	D	B	B	C
17	5%	6%	5%	8%	5%	6%		B	D	B	E	B	E	C	C	D
18	3%	5%	3%	7%	3%	5%		C	C	C	A	B	C	C	D	E
19	6%	4%	6%	6%	2%	4%		D	D	D	B	C	D	D	E	A
20	2%	3%	2%	5%	3%	3%		E	E	A	C	D	E	E	A	B
21	4%	2%	4%	4%	1%	2%		C	A	A	D	A	A	A	B	C
Data Sheet (2)

 

[SanjayGMusafir]

Check this and revert -

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	Nour Chen	Theresa Prentice	Montana Grimes	Piotr Vaughn	Myah Ballard	Keanan Espinosa		Armenia	Nigeria	India	USA	Honduras	Togo	Georgia	Mauritania	Trinidad and Tobago			EXPECTED RESULT		%
2	0.18	0.01	0.05	0.03	0.06	0.01		A	E	B	D	E	E	C	E	C					A	B	C	D	E
3	0.13	0.01	0.06	0.02	0.02	0.01		B	A	C	E	A	C	D	C	A			Armenia	Nour Chen	26%	25%	19%	17%	14%
4	0.05	0.005	0.08	0.01	0.04	0.18		C	B	D	C	B	D	E	D	B			Nigeria	Theresa Prentice	8%	15%	23%	33%	22%
5	0.06	0.005	0.07	0.01	0.05	0.13		D	C	E	D	C	E	A	E	C			India	Montana Grimes	13%	20%	14%	33%	21%
6	0.08	0.05	0.06	0.18	0.06	0.005		E	D	A	E	D	A	B	A	D			USA	Piotr Vaughn	22%	10%	21%	15%	33%
7	0.07	0.03	0.05	0.13	0.08	0.005		A	E	B	A	E	B	C	B	E			Honduras	Myah Ballard	4%	12%	36%	28%	21%
8	0.06	0.06	0.04	0.005	0.07	0.05		B	C	B	C	C	B	D	B	A			Togo	Keanan Espinosa	8%	12%	23%	31%	27%
9	0.05	0.02	0.03	0.005	0.06	0.03		C	E	C	D	D	C	E	C	B			Georgia	Montana Grimes	24%	7%	20%	18%	31%
10	0.04	0.04	0.02	0.05	0.05	0.06		D	C	C	E	E	C	C	D	C			Mauritania	Piotr Vaughn	28%	24%	21%	15%	13%
11	0.03	0.18	0.01	0.03	0.04	0.02		E	D	D	C	C	D	D	E	D			Trinidad and Tobago	Myah Ballard	11%	13%	36%	28%	13%
12	0.02	0.13	0.01	0.06	0.01	0.04		C	E	E	C	E	E	D	C	E
13	0.01	0.05	0.18	0.02	0.18	0.05		D	A	D	A	C	A	E	D	C
14	0.01	0.06	0.13	0.04	0.13	0.06		E	B	E	B	D	B	A	C	D
15	0.005	0.08	0.005	0.05	0.005	0.08		A	B	A	C	E	C	B	A	E
16	0.005	0.07	0.005	0.06	0.005	0.07		B	C	B	D	A	D	B	B	C
17	0.05	0.06	0.05	0.08	0.05	0.06		B	D	B	E	B	E	C	C	D
18	0.03	0.05	0.03	0.07	0.03	0.05		C	C	C	A	B	C	C	D	E
19	0.06	0.04	0.06	0.06	0.02	0.04		D	D	D	B	C	D	D	E	A
20	0.02	0.03	0.02	0.05	0.03	0.03		E	E	A	C	D	E	E	A	B
21	0.04	0.02	0.04	0.04	0.01	0.02		C	A	A	D	A	A	A	B	C
Sheet1

Cell Formulas
Range	Formula
U3:Y3	U3	=SUMIFS($A:$A,$H:$H,U$2)
U4:Y4	U4	=SUMIFS($B:$B,$I:$I,U$2)
U5:Y5	U5	=SUMIFS($C:$C,$J:$J,U$2)
U6:Y6	U6	=SUMIFS($D:$D,$K:$K,U$2)
U7:Y7	U7	=SUMIFS($E:$E,$L:$L,U$2)
U8:Y8	U8	=SUMIFS($F:$F,$M:$M,U$2)
U9:Y9	U9	=SUMIFS($C:$C,$N:$N,U$2)
U10:Y10	U10	=SUMIFS($D:$D,$O:$O,U$2)
U11:Y11	U11	=SUMIFS($E:$E,$P:$P,U$2)

 

[ExcelNewbie2020]

Check this and revert -

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	Nour Chen	Theresa Prentice	Montana Grimes	Piotr Vaughn	Myah Ballard	Keanan Espinosa		Armenia	Nigeria	India	USA	Honduras	Togo	Georgia	Mauritania	Trinidad and Tobago			EXPECTED RESULT		%
2	0.18	0.01	0.05	0.03	0.06	0.01		A	E	B	D	E	E	C	E	C					A	B	C	D	E
3	0.13	0.01	0.06	0.02	0.02	0.01		B	A	C	E	A	C	D	C	A			Armenia	Nour Chen	26%	25%	19%	17%	14%
4	0.05	0.005	0.08	0.01	0.04	0.18		C	B	D	C	B	D	E	D	B			Nigeria	Theresa Prentice	8%	15%	23%	33%	22%
5	0.06	0.005	0.07	0.01	0.05	0.13		D	C	E	D	C	E	A	E	C			India	Montana Grimes	13%	20%	14%	33%	21%
6	0.08	0.05	0.06	0.18	0.06	0.005		E	D	A	E	D	A	B	A	D			USA	Piotr Vaughn	22%	10%	21%	15%	33%
7	0.07	0.03	0.05	0.13	0.08	0.005		A	E	B	A	E	B	C	B	E			Honduras	Myah Ballard	4%	12%	36%	28%	21%
8	0.06	0.06	0.04	0.005	0.07	0.05		B	C	B	C	C	B	D	B	A			Togo	Keanan Espinosa	8%	12%	23%	31%	27%
9	0.05	0.02	0.03	0.005	0.06	0.03		C	E	C	D	D	C	E	C	B			Georgia	Montana Grimes	24%	7%	20%	18%	31%
10	0.04	0.04	0.02	0.05	0.05	0.06		D	C	C	E	E	C	C	D	C			Mauritania	Piotr Vaughn	28%	24%	21%	15%	13%
11	0.03	0.18	0.01	0.03	0.04	0.02		E	D	D	C	C	D	D	E	D			Trinidad and Tobago	Myah Ballard	11%	13%	36%	28%	13%
12	0.02	0.13	0.01	0.06	0.01	0.04		C	E	E	C	E	E	D	C	E
13	0.01	0.05	0.18	0.02	0.18	0.05		D	A	D	A	C	A	E	D	C
14	0.01	0.06	0.13	0.04	0.13	0.06		E	B	E	B	D	B	A	C	D
15	0.005	0.08	0.005	0.05	0.005	0.08		A	B	A	C	E	C	B	A	E
16	0.005	0.07	0.005	0.06	0.005	0.07		B	C	B	D	A	D	B	B	C
17	0.05	0.06	0.05	0.08	0.05	0.06		B	D	B	E	B	E	C	C	D
18	0.03	0.05	0.03	0.07	0.03	0.05		C	C	C	A	B	C	C	D	E
19	0.06	0.04	0.06	0.06	0.02	0.04		D	D	D	B	C	D	D	E	A
20	0.02	0.03	0.02	0.05	0.03	0.03		E	E	A	C	D	E	E	A	B
21	0.04	0.02	0.04	0.04	0.01	0.02		C	A	A	D	A	A	A	B	C
Sheet1

Cell Formulas
Range	Formula
U3:Y3	U3	=SUMIFS($A:$A,$H:$H,U$2)
U4:Y4	U4	=SUMIFS($B:$B,$I:$I,U$2)
U5:Y5	U5	=SUMIFS($C:$C,$J:$J,U$2)
U6:Y6	U6	=SUMIFS($D:$D,$K:$K,U$2)
U7:Y7	U7	=SUMIFS($E:$E,$L:$L,U$2)
U8:Y8	U8	=SUMIFS($F:$F,$M:$M,U$2)
U9:Y9	U9	=SUMIFS($C:$C,$N:$N,U$2)
U10:Y10	U10	=SUMIFS($D:$D,$O:$O,U$2)
U11:Y11	U11	=SUMIFS($E:$E,$P:$P,U$2)

thanks man, i tried that solution already..it works.. but i am looking for a formula that will consider the criteria under column S & T..

 

[SanjayGMusafir]

but i am looking for a formula that will consider the criteria under column S & T.

Are you looking for this...

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	Nour Chen	Theresa Prentice	Montana Grimes	Piotr Vaughn	Myah Ballard	Keanan Espinosa		Armenia	Nigeria	India	USA	Honduras	Togo	Georgia	Mauritania	Trinidad and Tobago			EXPECTED RESULT		%
2	0.18	0.01	0.05	0.03	0.06	0.01		A	E	B	D	E	E	C	E	C					A	B	C	D	E
3	0.13	0.01	0.06	0.02	0.02	0.01		B	A	C	E	A	C	D	C	A			Armenia	Nour Chen	26%	25%	19%	17%	14%
4	0.05	0.005	0.08	0.01	0.04	0.18		C	B	D	C	B	D	E	D	B			Nigeria	Theresa Prentice	8%	15%	23%	33%	22%
5	0.06	0.005	0.07	0.01	0.05	0.13		D	C	E	D	C	E	A	E	C			India	Montana Grimes	13%	20%	14%	33%	21%
6	0.08	0.05	0.06	0.18	0.06	0.005		E	D	A	E	D	A	B	A	D			USA	Piotr Vaughn	22%	10%	21%	15%	33%
7	0.07	0.03	0.05	0.13	0.08	0.005		A	E	B	A	E	B	C	B	E			Honduras	Myah Ballard	4%	12%	36%	28%	21%
8	0.06	0.06	0.04	0.005	0.07	0.05		B	C	B	C	C	B	D	B	A			Togo	Keanan Espinosa	8%	12%	23%	31%	27%
9	0.05	0.02	0.03	0.005	0.06	0.03		C	E	C	D	D	C	E	C	B			Georgia	Montana Grimes	24%	7%	20%	18%	31%
10	0.04	0.04	0.02	0.05	0.05	0.06		D	C	C	E	E	C	C	D	C			Mauritania	Piotr Vaughn	28%	24%	21%	15%	13%
11	0.03	0.18	0.01	0.03	0.04	0.02		E	D	D	C	C	D	D	E	D			Trinidad and Tobago	Myah Ballard	11%	13%	36%	28%	13%
12	0.02	0.13	0.01	0.06	0.01	0.04		C	E	E	C	E	E	D	C	E
13	0.01	0.05	0.18	0.02	0.18	0.05		D	A	D	A	C	A	E	D	C
14	0.01	0.06	0.13	0.04	0.13	0.06		E	B	E	B	D	B	A	C	D
15	0.005	0.08	0.005	0.05	0.005	0.08		A	B	A	C	E	C	B	A	E
16	0.005	0.07	0.005	0.06	0.005	0.07		B	C	B	D	A	D	B	B	C
17	0.05	0.06	0.05	0.08	0.05	0.06		B	D	B	E	B	E	C	C	D
18	0.03	0.05	0.03	0.07	0.03	0.05		C	C	C	A	B	C	C	D	E
19	0.06	0.04	0.06	0.06	0.02	0.04		D	D	D	B	C	D	D	E	A
20	0.02	0.03	0.02	0.05	0.03	0.03		E	E	A	C	D	E	E	A	B
21	0.04	0.02	0.04	0.04	0.01	0.02		C	A	A	D	A	A	A	B	C
Sheet1

Cell Formulas
Range	Formula
U3:Y11	U3	=SUM(INDEX($A$2:$F$1000,,MATCH($T3,$A$1:$F$1,0))*((INDEX($A$2:$P$1000,,MATCH($S3,$A$1:$P$1,0))=U$2)*1))

 

[eiloken]

try this:

U3=SUMIFS(OFFSET($A$1,0,MATCH($T3,$A$1:$F$1,0)-1,21),OFFSET($H$1,0,MATCH($S3,$H$1:$P$1,0)-1,21),U$2)

 

[ExcelNewbie2020]

Are you looking for this...

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	Nour Chen	Theresa Prentice	Montana Grimes	Piotr Vaughn	Myah Ballard	Keanan Espinosa		Armenia	Nigeria	India	USA	Honduras	Togo	Georgia	Mauritania	Trinidad and Tobago			EXPECTED RESULT		%
2	0.18	0.01	0.05	0.03	0.06	0.01		A	E	B	D	E	E	C	E	C					A	B	C	D	E
3	0.13	0.01	0.06	0.02	0.02	0.01		B	A	C	E	A	C	D	C	A			Armenia	Nour Chen	26%	25%	19%	17%	14%
4	0.05	0.005	0.08	0.01	0.04	0.18		C	B	D	C	B	D	E	D	B			Nigeria	Theresa Prentice	8%	15%	23%	33%	22%
5	0.06	0.005	0.07	0.01	0.05	0.13		D	C	E	D	C	E	A	E	C			India	Montana Grimes	13%	20%	14%	33%	21%
6	0.08	0.05	0.06	0.18	0.06	0.005		E	D	A	E	D	A	B	A	D			USA	Piotr Vaughn	22%	10%	21%	15%	33%
7	0.07	0.03	0.05	0.13	0.08	0.005		A	E	B	A	E	B	C	B	E			Honduras	Myah Ballard	4%	12%	36%	28%	21%
8	0.06	0.06	0.04	0.005	0.07	0.05		B	C	B	C	C	B	D	B	A			Togo	Keanan Espinosa	8%	12%	23%	31%	27%
9	0.05	0.02	0.03	0.005	0.06	0.03		C	E	C	D	D	C	E	C	B			Georgia	Montana Grimes	24%	7%	20%	18%	31%
10	0.04	0.04	0.02	0.05	0.05	0.06		D	C	C	E	E	C	C	D	C			Mauritania	Piotr Vaughn	28%	24%	21%	15%	13%
11	0.03	0.18	0.01	0.03	0.04	0.02		E	D	D	C	C	D	D	E	D			Trinidad and Tobago	Myah Ballard	11%	13%	36%	28%	13%
12	0.02	0.13	0.01	0.06	0.01	0.04		C	E	E	C	E	E	D	C	E
13	0.01	0.05	0.18	0.02	0.18	0.05		D	A	D	A	C	A	E	D	C
14	0.01	0.06	0.13	0.04	0.13	0.06		E	B	E	B	D	B	A	C	D
15	0.005	0.08	0.005	0.05	0.005	0.08		A	B	A	C	E	C	B	A	E
16	0.005	0.07	0.005	0.06	0.005	0.07		B	C	B	D	A	D	B	B	C
17	0.05	0.06	0.05	0.08	0.05	0.06		B	D	B	E	B	E	C	C	D
18	0.03	0.05	0.03	0.07	0.03	0.05		C	C	C	A	B	C	C	D	E
19	0.06	0.04	0.06	0.06	0.02	0.04		D	D	D	B	C	D	D	E	A
20	0.02	0.03	0.02	0.05	0.03	0.03		E	E	A	C	D	E	E	A	B
21	0.04	0.02	0.04	0.04	0.01	0.02		C	A	A	D	A	A	A	B	C
Sheet1

Cell Formulas
Range	Formula
U3:Y11	U3	=SUM(INDEX($A$2:$F$1000,,MATCH($T3,$A$1:$F$1,0))*((INDEX($A$2:$P$1000,,MATCH($S3,$A$1:$P$1,0))=U$2)*1))

yes..it worx.. thanks man..

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.

 

[Peter_SSs]

Another option (I would avoid the volatile function OFFSET option).

23 11 23.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	Nour Chen	Theresa Prentice	Montana Grimes	Piotr Vaughn	Myah Ballard	Keanan Espinosa		Armenia	Nigeria	India	USA	Honduras	Togo	Georgia	Mauritania	Trinidad and Tobago			EXPECTED RESULT		%
2	18%	1%	5%	3%	6%	1%		A	E	B	D	E	E	C	E	C					A	B	C	D	E
3	13%	1%	6%	2%	2%	1%		B	A	C	E	A	C	D	C	A			Armenia	Nour Chen	26%	25%	19%	17%	14%
4	5%	1%	8%	1%	4%	18%		C	B	D	C	B	D	E	D	B			Nigeria	Theresa Prentice	8%	15%	23%	33%	22%
5	6%	1%	7%	1%	5%	13%		D	C	E	D	C	E	A	E	C			India	Montana Grimes	13%	20%	14%	33%	21%
6	8%	5%	6%	18%	6%	1%		E	D	A	E	D	A	B	A	D			USA	Piotr Vaughn	22%	10%	21%	15%	33%
7	7%	3%	5%	13%	8%	1%		A	E	B	A	E	B	C	B	E			Honduras	Myah Ballard	4%	12%	36%	28%	21%
8	6%	6%	4%	1%	7%	5%		B	C	B	C	C	B	D	B	A			Togo	Keanan Espinosa	8%	12%	23%	31%	27%
9	5%	2%	3%	1%	6%	3%		C	E	C	D	D	C	E	C	B			Georgia	Montana Grimes	24%	7%	20%	18%	31%
10	4%	4%	2%	5%	5%	6%		D	C	C	E	E	C	C	D	C			Mauritania	Piotr Vaughn	28%	24%	21%	15%	13%
11	3%	18%	1%	3%	4%	2%		E	D	D	C	C	D	D	E	D			Trinidad and Tobago	Myah Ballard	11%	13%	36%	28%	13%
12	2%	13%	1%	6%	1%	4%		C	E	E	C	E	E	D	C	E
13	1%	5%	18%	2%	18%	5%		D	A	D	A	C	A	E	D	C
14	1%	6%	13%	4%	13%	6%		E	B	E	B	D	B	A	C	D
15	1%	8%	1%	5%	1%	8%		A	B	A	C	E	C	B	A	E
16	1%	7%	1%	6%	1%	7%		B	C	B	D	A	D	B	B	C
17	5%	6%	5%	8%	5%	6%		B	D	B	E	B	E	C	C	D
18	3%	5%	3%	7%	3%	5%		C	C	C	A	B	C	C	D	E
19	6%	4%	6%	6%	2%	4%		D	D	D	B	C	D	D	E	A
20	2%	3%	2%	5%	3%	3%		E	E	A	C	D	E	E	A	B
21	4%	2%	4%	4%	1%	2%		C	A	A	D	A	A	A	B	C
Sum

Cell Formulas
Range	Formula
U3:Y11	U3	=LET(r,$A$2:$P$21,h,$A$1:$P$1,SUM(FILTER(FILTER(r,h=$T3),FILTER(r,h=$S3)=U$2,0)))