Sum top 2 scores from across multiple sheets

[SirScott13]

I have a spreadsheet with 6 tabs. Each tab contains the results from a climbing competition. The columns on the tabs are Rank (1-40), Name, & Points (variable). The goal of the spreadsheet is to compile a list of all climbers along with a total of their top 2 highest point totals.

I made an attempt at compiling all of the results into a single tab, but I am having trouble getting the Top 2 distinct results per climber.

Is there a formula that will make this easy? I have looked at the LARGE function, but I don't know how to tie this to the name in the other column.

Any assistance/guidance would be appreciated. You guys helped me a ton on a work project years ago.

 

[ChrisFoster]

I have a spreadsheet with 6 tabs. Each tab contains the results from a climbing competition. The columns on the tabs are Rank (1-40), Name, & Points (variable). The goal of the spreadsheet is to compile a list of all climbers along with a total of their top 2 highest point totals.

I made an attempt at compiling all of the results into a single tab, but I am having trouble getting the Top 2 distinct results per climber.

Is there a formula that will make this easy? I have looked at the LARGE function, but I don't know how to tie this to the name in the other column.

Any assistance/guidance would be appreciated. You guys helped me a ton on a work project years ago.

Can you post an example of the spreadsheet you are using?
MAXIF formula may be of use as that finds the highest value given a specific criteria - but if you need the top 2, then I'd have to work something different out.

 

[SirScott13]

Climbing Rankings 2021 Test.xlsx
	A	B	C
1
2
3	FJR
4	Rank	Name	Points
5	1	Dwayne Johnson	100
6	2	Steve Williams	80
7	3	Steve Jobs	70
8	4	Daryll Hannah	60
9	5	Dwayne Johnson	50
10	6	Dwayne Johnson	10
11	7	Steve Jobs	60
12	8	Steve Jobs	20
13	9	0	0
14	10	0	0
15	11	0	0
16	12	0	0
17	13	0	0
18	14	0	0
19	15	0	0
20	16	0	0
21	17	0	0
22	18	0	0
23	19	0	0
24	20	0	0
25	21	0	0
26	22	0	0
27	23	0	0
28	24	0	0
29	25	0	0
30	26	0	0
31	27	0	0
32	28	0	0
33	29	0	0
34	30	0	0
35	31	0	0
36	32	0	0
37	33	0	0
38	34	0	0
39	35	0	0
40	36	0	0
41	37	0	0
42	38	0	0
43	39	0	0
44	40	0	0
45	1	Daryll Hannah	100
46	2	Steve Jobs	80
47	3	Dwayne Johnson	70
48	4	Steve Jobs	50
49	5	Steve Williams	40
50	6	Donald Trump	30
51	7	Donald Trump	10
52	8	0	0
53	9	0	0
54	10	0	0
55	11	0	0
56	12	0	0
57	13	0	0
58	14	0	0
59	15	0	0
60	16	0	0
61	17	0	0
62	18	0	0
63	19	0	0
64	20	0	0
65	21	0	0
66	22	0	0
67	23	0	0
68	24	0	0
69	25	0	0
70	26	0	0
71	27	0	0
72	28	0	0
73	29	0	0
74	30	0	0
75	31	0	0
76	32	0	0
77	33	0	0
78	34	0	0
79	35	0	0
80	36	0	0
81	37	0	0
82	38	0	0
83	39	0	0
84	40	0	0
85	1	Steve Jobs	40
86	2	Steve Jobs	30
87	3	Steve Williams	30
88	4	0	0
89	5	0	0
90	6	0	0
91	7	0	0
92	8	0	0
93	9	0	0
94	10	0	0
95	11	0	0
96	12	0	0
97	13	0	0
98	14	0	0
99	15	0	0
100	16	0	0
101	17	0	0
102	18	0	0
103	19	0	0
104	20	0	0
105	21	0	0
106	22	0	0
107	23	0	0
108	24	0	0
109	25	0	0
110	26	0	0
111	27	0	0
112	28	0	0
113	29	0	0
114	30	0	0
115	31	0	0
116	32	0	0
117	33	0	0
118	34	0	0
119	35	0	0
120	36	0	0
121	37	0	0
122	38	0	0
123	39	0	0
124	40	0	0
125	1	Donald Trump	10
126	2	Donald Trump	20
127	3	Donald Trump	100
128	4	0	0
129	5	0	0
130	6	0	0
131	7	0	0
132	8	0	0
133	9	0	0
134	10	0	0
135	11	0	0
136	12	0	0
137	13	0	0
138	14	0	0
139	15	0	0
140	16	0	0
141	17	0	0
142	18	0	0
143	19	0	0
144	20	0	0
145	21	0	0
146	22	0	0
147	23	0	0
148	24	0	0
149	25	0	0
150	26	0	0
151	27	0	0
152	28	0	0
153	29	0	0
154	30	0	0
155	31	0	0
156	32	0	0
157	33	0	0
158	34	0	0
159	35	0	0
160	36	0	0
161	37	0	0
162	38	0	0
163	39	0	0
164	40	0	0
165	1	Steve Wiliams	100
166	2	Donald Trump	40
167	3	Dwayne Johnson	100
168	4	0	0
169	5	0	0
170	6	0	0
171	7	0	0
172	8	0	0
173	9	0	0
174	10	0	0
175	11	0	0
176	12	0	0
177	13	0	0
178	14	0	0
179	15	0	0
180	16	0	0
181	17	0	0
182	18	0	0
183	19	0	0
184	20	0	0
185	21	0	0
186	22	0	0
187	23	0	0
188	24	0	0
189	25	0	0
190	26	0	0
191	27	0	0
192	28	0	0
193	29	0	0
194	30	0	0
195	31	0	0
196	32	0	0
197	33	0	0
198	34	0	0
199	35	0	0
200	36	0	0
201	37	0	0
202	38	0	0
203	39	0	0
204	40	0	0
205	1	Steve Williams	100
206	2	0	0
207	3	0	0
208	4	0	0
209	5	0	0
210	6	0	0
211	7	0	0
212	8	0	0
213	9	0	0
214	10	0	0
215	11	0	0
216	12	0	0
217	13	0	0
218	14	0	0
219	15	0	0
220	16	0	0
221	17	0	0
222	18	0	0
223	19	0	0
224	20	0	0
225	21	0	0
226	22	0	0
227	23	0	0
228	24	0	0
229	25	0	0
230	26	0	0
231	27	0	0
232	28	0	0
233	29	0	0
234	30	0	0
235	31	0	0
236	32	0	0
237	33	0	0
238	34	0	0
239	35	0	0
240	36	0	0
241	37	0	0
242	38	0	0
243	39	0	0
244	40	0	0
Consolidated Data

Cell Formulas
Range	Formula
A5:C44	A5	='High Point'!A5
A45:C84	A45	=Ozark!A5
A85:C124	A85	='Upper Limits'!A5
A125:C164	A125	=RoKC!A5
A165:C204	A165	='The Crag'!A5
A205:C243,A244:B244	A205	='High Point Cleveland'!A5
C244	C244	=A14:C244='High Point Cleveland'!C44

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[SirScott13]

Thanks for the reminder. I've added this info to my Account Details.

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2
3	FJR
4	Rank	Name	Points
5	1	Dwayne Johnson	100			Dwayne Johnson	100	100
6	2	Steve Williams	80			Steve Williams	100	100
7	3	Steve Jobs	70			Steve Jobs	80	70
8	4	Daryll Hannah	60			Daryll Hannah	100	60
9	5	Dwayne Johnson	50			Donald Trump	100	40
10	6	Dwayne Johnson	10
11	7	Steve Jobs	60
12	8	Steve Jobs	20
45	1	Daryll Hannah	100
46	2	Steve Jobs	80
47	3	Dwayne Johnson	70
48	4	Steve Jobs	50
49	5	Steve Williams	40
50	6	Donald Trump	30
51	7	Donald Trump	10
85	1	Steve Jobs	40
86	2	Steve Jobs	30
87	3	Steve Williams	30
125	1	Donald Trump	10
126	2	Donald Trump	20
127	3	Donald Trump	100
165	1	Steve Williams	100
166	2	Donald Trump	40
167	3	Dwayne Johnson	100
205	1	Steve Williams	100
244	40	0	0
245
Lists

Cell Formulas
Range	Formula
F5:F9	F5	=UNIQUE(FILTER(B5:B250,B5:B250<>0))
G5:H9	G5	=INDEX(SORT(FILTER($C$2:$C$250,$B$2:$B$250=F5),,-1),{1,2})
Dynamic array formulas.

 

[SirScott13]

That looks like exactly what I am looking for. That's amazing. Thanks so much for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[SirScott13]

I have a follow-up question. Is there a way to default Score 2 to '0' if there is only one valid score? Right now I am getting a #Ref! error when I only have 1 score.

Name	Sum	1st Score	2nd Score
Steve Williams	165	100	65
Bill Gates	135	100	35
**** Trickle	122	80	42
Bob Barker	165	100	65
Jacob Degrom	210	110	100
David Johnson	1040	1000	40
Jason Johnson	180	100	80
scott	#REF!	10	#REF!

 

[Fluff]

Yup, you can use

=IFERROR(INDEX(SORT(FILTER($C$2:$C$250,$B$2:$B$250=F5),,-1),{1,2}),0)