Sum results from 3 ranges of data - but not all names are in the 3 ranges

[Marklarbear]

Hi Brains Trust,

I'm having brain fog trying to work this one out.... I have 3 ranges of data where player results are recorded. What i'm trying to do is automatically sum the results from the 3 ranges in the bottom range (A22:I39).

But I only want to sum the results of those that are listed in the bottom range. How can I do this? I've highlighted yellow the names that are in the top 3 ranges that ARE NOT to be included in the sum range below.

Hope this makes sense.....

 

[Cubist]

Try something like 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	Z	AA	AB	AC
1
2		1st	2nd	3rd	4th	5th	A	B	C			1st	2nd	3rd	4th	5th	A	B	C			1st	2nd	3rd	4th	5th	A	B	C
3	A	455	424	431	177	420	220	429	114		L	363	80	235	451	487	395	367	386		A	224	136	432	169	453	77	158	475
4	B	482	70	303	496	434	443	429	243		M	218	259	126	289	425	293	170	332		B	334	401	374	170	313	128	237	140
5	C	181	477	382	236	218	434	294	417		N	62	464	73	61	328	231	476	74		C	160	72	380	433	247	355	363	264
6	D	51	392	143	377	156	268	248	447		O	116	262	136	238	249	284	412	336		D	278	112	470	99	283	260	180	425
7	E	261	271	476	262	228	324	428	488		P	493	200	120	191	417	462	418	97		E	253	72	475	70	195	474	150	101
8	F	332	53	99	392	442	131	459	349		Q	173	391	125	130	198	411	482	454		F	438	445	250	87	378	211	236	337
9	G	185	496	422	165	359	283	185	481		R	401	140	141	165	403	312	346	416		R	217	235	353	119	151	200	442	442
10	H	353	309	393	262	128	465	206	164		S	147	430	319	351	240	137	346	63		S	376	467	409	138	61	317	318	151
11	I	483	73	362	265	451	72	167	70		T	264	386	74	270	333	278	495	463		T	120	375	485	311	259	396	95	353
12	J	335	287	141	471	441	298	323	107		U	77	172	62	205	457	416	401	260		U	315	140	237	443	60	164	148	174
13
14		1st	2nd	3rd	4th	5th	A	B	C
15	A	679	560	863	346	873	297	587	589
16	B	816	471	677	666	747	571	666	383
17	C	341	549	762	669	465	789	657	681
18	D	329	504	613	476	439	528	428	872
19	S	523	897	728	489	301	454	664	214
20	T	384	761	559	581	592	674	590	816
Sheet3

Cell Formulas
Range	Formula
B15:I20	B15	=LET(v,VSTACK($A$3:$I$12,$K$3:$S$12,$U$3:$AC$12),f,FILTER(DROP(v,0,1),CHOOSECOLS(v,1)=A15),BYCOL(f,LAMBDA(bc,SUM(bc))))
Dynamic array formulas.

 

[Marklarbear]

Try something like 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	Z	AA	AB	AC
1
2		1st	2nd	3rd	4th	5th	A	B	C			1st	2nd	3rd	4th	5th	A	B	C			1st	2nd	3rd	4th	5th	A	B	C
3	A	455	424	431	177	420	220	429	114		L	363	80	235	451	487	395	367	386		A	224	136	432	169	453	77	158	475
4	B	482	70	303	496	434	443	429	243		M	218	259	126	289	425	293	170	332		B	334	401	374	170	313	128	237	140
5	C	181	477	382	236	218	434	294	417		N	62	464	73	61	328	231	476	74		C	160	72	380	433	247	355	363	264
6	D	51	392	143	377	156	268	248	447		O	116	262	136	238	249	284	412	336		D	278	112	470	99	283	260	180	425
7	E	261	271	476	262	228	324	428	488		P	493	200	120	191	417	462	418	97		E	253	72	475	70	195	474	150	101
8	F	332	53	99	392	442	131	459	349		Q	173	391	125	130	198	411	482	454		F	438	445	250	87	378	211	236	337
9	G	185	496	422	165	359	283	185	481		R	401	140	141	165	403	312	346	416		R	217	235	353	119	151	200	442	442
10	H	353	309	393	262	128	465	206	164		S	147	430	319	351	240	137	346	63		S	376	467	409	138	61	317	318	151
11	I	483	73	362	265	451	72	167	70		T	264	386	74	270	333	278	495	463		T	120	375	485	311	259	396	95	353
12	J	335	287	141	471	441	298	323	107		U	77	172	62	205	457	416	401	260		U	315	140	237	443	60	164	148	174
13
14		1st	2nd	3rd	4th	5th	A	B	C
15	A	679	560	863	346	873	297	587	589
16	B	816	471	677	666	747	571	666	383
17	C	341	549	762	669	465	789	657	681
18	D	329	504	613	476	439	528	428	872
19	S	523	897	728	489	301	454	664	214
20	T	384	761	559	581	592	674	590	816
Sheet3

Cell Formulas
Range	Formula
B15:I20	B15	=LET(v,VSTACK($A$3:$I$12,$K$3:$S$12,$U$3:$AC$12),f,FILTER(DROP(v,0,1),CHOOSECOLS(v,1)=A15),BYCOL(f,LAMBDA(bc,SUM(bc))))
Dynamic array formulas.

That worked brilliantly - your a legend..... Thankyou

 

[Fluff]

Another option is

=SUMIFS(B$3:AC$12,$A$3:$AB$12,$A15)

 

[Cubist]

Another option is

=SUMIFS(B$3:AC$12,$A$3:$AB$12,$A15)

I tried something similar but couldn't get the ranges right. Neat.