Sum of last 'n' values from two different columns

C

chrisb182

New Member
Joined
Mar 3, 2022
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
Hi Everyone,

From the below information, I would like to sum the last 'n' values based from the TE columns (K and R) depending on which team I select in another cell.

For example:
I select the team "NO" and want to sum up the last two values --- the formula would sum up 25.46 from Column R and 19.7 from Column K.

Does anyone know how to do this?

I really hope I'm making my request clear.

Cheers,

Chris

1669133722370.png
 
Sort by date Sort by votes

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You need to select the whole range, not just a single cell.
 
Upvote 0
Whoops..... my bad. Apologies.

Mr Excel.xlsx
ABCDEGHJKLMNOPQ
1WeekAwayHomeLast3
2WeekDayDateTimeAwayHomeTETETeamTE
31Thu08-Sep-228:20PMBUF@LAR1.58.9ARI
41Sun11-Sep-221:00PMNO@ATL21.65.4ATL
51Sun11-Sep-221:00PMPHI@DET97.8BAL
61Sun11-Sep-221:00PMJAX@WAS6.810.8BUF
71Sun11-Sep-221:00PMIND@HOU9.822CAR
81Sun11-Sep-221:00PMPIT@CIN18.510.8CHI
91Sun11-Sep-221:00PMBAL@NYJ10.211.4CIN
101Sun11-Sep-221:00PMSF@CHI40CLE
111Sun11-Sep-221:00PMNE@MIA10.33.5DAL
121Sun11-Sep-221:00PMCLE@CAR5.58.4DEN
131Sun11-Sep-224:25PMNYG@TEN7.18.2DET
141Sun11-Sep-224:25PMLVR@LAC11.919.8GB
151Sun11-Sep-224:25PMKC@ARI35.211.4HOU
161Sun11-Sep-224:25PMGB@MIN12.75.9IND
171Sun11-Sep-228:20PMTB@DAL1.713.2JAX
181Mon12-Sep-228:20PMDEN@SEA15.530.2KC
192Thu15-Sep-228:15PMLAC@KC13.113.3LAC
202Sun18-Sep-221:00PMWAS@DET16.29.1LAR
212Sun18-Sep-221:00PMMIA@BAL15.634LVR
222Sun18-Sep-221:00PMTB@NO3.39.4MIA
232Sun18-Sep-221:00PMNE@PIT012.2MIN
242Sun18-Sep-221:00PMIND@JAX5.313.4NE
252Sun18-Sep-221:00PMNYJ@CLE1013.7NO
262Sun18-Sep-221:00PMCAR@NYG4.68.6NYG
272Sun18-Sep-224:05PMSEA@SF5.110.8NYJ
282Sun18-Sep-224:05PMATL@LAR5.414.1PHI
292Sun18-Sep-224:25PMHOU@DEN9.49.1PIT
302Sun18-Sep-224:25PMARI@LVR16.723SEA
312Sun18-Sep-224:25PMCIN@DAL9.31.8SF
322Sun18-Sep-228:20PMCHI@GB2.83.1TB
332Mon19-Sep-227:15PMTEN@BUF2.99.7TEN
342Mon19-Sep-228:30PMMIN@PHI19.413.2WAS
353Thu22-Sep-228:15PMPIT@CLE6.123.9
363Sun25-Sep-221:00PMBUF@MIA10.76.9
373Sun25-Sep-221:00PMCIN@NYJ3.617.9
383Sun25-Sep-221:00PMDET@MIN10.813.9
393Sun25-Sep-221:00PMHOU@CHI6.16
403Sun25-Sep-221:00PMKC@IND21.519.9
413Sun25-Sep-221:00PMNO@CAR3.45
423Sun25-Sep-221:00PMPHI@WAS182.5
433Sun25-Sep-221:00PMLVR@TEN12.614.8
443Sun25-Sep-221:00PMBAL@NE39.58.3
453Sun25-Sep-224:05PMJAX@LAC3.94.5
463Sun25-Sep-224:25PMATL@SEA13.725.5
473Sun25-Sep-224:25PMGB@TB14.312.4
483Sun25-Sep-224:25PMLAR@ARI10.110.5
493Sun25-Sep-228:20PMSF@DEN6.82.2
503Mon26-Sep-228:15PMDAL@NYG10.69.6
514Thu29-Sep-228:15PMMIA@CIN4.314.5
524Sun02-Oct-229:30AMMIN@NO5.321.1
534Sun02-Oct-221:00PMBUF@BAL73.5
544Sun02-Oct-221:00PMCHI@NYG7.98.1
554Sun02-Oct-221:00PMCLE@ATL15.56.7
564Sun02-Oct-221:00PMJAX@PHI7.212.2
574Sun02-Oct-221:00PMNYJ@PIT8.216.7
584Sun02-Oct-221:00PMTEN@IND14.241
594Sun02-Oct-221:00PMLAC@HOU18.510
604Sun02-Oct-221:00PMSEA@DET25.939.9
614Sun02-Oct-221:00PMWAS@DAL12.81.7
624Sun02-Oct-224:05PMARI@CAR22.18
634Sun02-Oct-224:25PMDEN@LVR3.55.4
644Sun02-Oct-224:25PMNE@GB3.310.2
654Sun02-Oct-228:20PMKC@TB38.316
664Mon03-Oct-228:15PMLAR@SF194.4
675Thu06-Oct-228:15PMIND@DEN4.310.1
685Sun09-Oct-229:30AMNYG@GB11.818.1
695Sun09-Oct-221:00PMATL@TB410.3
705Sun09-Oct-221:00PMCHI@MIN9.610.8
715Sun09-Oct-221:00PMDET@NE1.69.4
725Sun09-Oct-221:00PMHOU@JAX4.219.4
735Sun09-Oct-221:00PMMIA@NYJ11.93.2
745Sun09-Oct-221:00PMTEN@WAS3.26.9
755Sun09-Oct-221:00PMPIT@BUF15.74.9
765Sun09-Oct-221:00PMLAC@CLE1.214.8
775Sun09-Oct-221:00PMSEA@NO1247.58
785Sun09-Oct-224:05PMSF@CAR7.72.8
795Sun09-Oct-224:25PMDAL@LAR011.6
805Sun09-Oct-224:25PMPHI@ARI17.510.8
815Sun09-Oct-228:20PMCIN@BAL17.325.6
825Mon10-Oct-228:15PMLVR@KC4.935.2
836Thu13-Oct-228:15PMWAS@CHI4.32.5
846Sun16-Oct-221:00PMCIN@NO5.114.44
856Sun16-Oct-221:00PMJAX@IND11.118.5
866Sun16-Oct-221:00PMMIN@MIA12.824.9
876Sun16-Oct-221:00PMNE@CLE24.217.7
886Sun16-Oct-221:00PMNYJ@GB6.420.9
896Sun16-Oct-221:00PMBAL@NYG32.714.8
906Sun16-Oct-221:00PMSF@ATL16.318.1
916Sun16-Oct-221:00PMTB@PIT7.78.3
926Sun16-Oct-224:05PMCAR@LAR4.21.7
936Sun16-Oct-224:05PMARI@SEA1415.8
946Sun16-Oct-224:25PMBUF@KC14.423.5
956Sun16-Oct-228:20PMDAL@PHI18.27.3
966Mon17-Oct-228:15PMDEN@LAC4.316.2
977Thu20-Oct-228:15PMNO@ARI30.326.1
987Sun23-Oct-221:00PMATL@CIN3.914.2
997Sun23-Oct-221:00PMCLE@BAL17.63
1007Sun23-Oct-221:00PMIND@TEN38.6
1017Sun23-Oct-221:00PMDET@DAL18.520.1
1027Sun23-Oct-221:00PMGB@WAS6.25.8
1037Sun23-Oct-221:00PMNYG@JAX6.414.1
1047Sun23-Oct-221:00PMTB@CAR1511.2
1057Sun23-Oct-224:05PMHOU@LVR9.85.8
1067Sun23-Oct-224:05PMNYJ@DEN7.715.2
1077Sun23-Oct-224:25PMKC@SF19.621.8
1087Sun23-Oct-224:25PMSEA@LAC1411.3
1097Sun23-Oct-228:20PMPIT@MIA19.15.7
1107Mon24-Oct-228:15PMCHI@NE6.73.6
1118Thu27-Oct-228:15PMBAL@TB27.45.4
1128Sun30-Oct-229:30AMDEN@JAX12.718.2
1138Sun30-Oct-221:00PMCAR@ATL9.220.9
1148Sun30-Oct-221:00PMCHI@DAL9.124
1158Sun30-Oct-221:00PMARI@MIN13.413.9
1168Sun30-Oct-221:00PMMIA@DET12.815
1178Sun30-Oct-221:00PMNE@NYJ7.229.6
1188Sun30-Oct-221:00PMPIT@PHI1112.4
1198Sun30-Oct-221:00PMLVR@NO9.113.48
1208Sun30-Oct-224:05PMTEN@HOU2.89.8
1218Sun30-Oct-224:25PMNYG@SEA10.88.6
1228Sun30-Oct-224:25PMSF@LAR21.13.5
1238Sun30-Oct-224:25PMWAS@IND3.94.1
1248Sun30-Oct-228:20PMGB@BUF10.49
1258Mon31-Oct-228:15PMCIN@CLE9.50
1269Thu03-Nov-228:15PMPHI@HOU26.33.9
1279Sun06-Nov-221:00PMBUF@NYJ5.56.3
1289Sun06-Nov-221:00PMCAR@CIN7.18.5
1299Sun06-Nov-221:00PMIND@NE4.514.1
1309Sun06-Nov-221:00PMGB@DET17.931.9
1319Sun06-Nov-221:00PMMIA@CHI1.324
1329Sun06-Nov-221:00PMMIN@WAS1.13.9
1339Sun06-Nov-221:00PMLVR@JAX4.48.9
1349Sun06-Nov-221:00PMLAC@ATL8.66.3
1359Sun06-Nov-224:05PMSEA@ARI22.615
1369Sun06-Nov-224:25PMLAR@TB019.4
1379Sun06-Nov-228:20PMTEN@KC11.228.1
1389Mon07-Nov-228:15PMBAL@NO12.315.12
13910Thu10-Nov-228:15PMATL@CAR6.40
14010Sun13-Nov-229:30AMSEA@TB12.98.3
14110Sun13-Nov-221:00PMCLE@MIA13.85.1
14210Sun13-Nov-221:00PMDEN@TEN4.314.2
14310Sun13-Nov-221:00PMDET@CHI8.623.4
14410Sun13-Nov-221:00PMHOU@NYG10.214.3
14510Sun13-Nov-221:00PMJAX@KC6.229.7
14610Sun13-Nov-221:00PMMIN@BUF11.59.7
14710Sun13-Nov-221:00PMNO@PIT19.710.5
14810Sun13-Nov-224:05PMIND@LVR9.713.3
14910Sun13-Nov-224:25PMARI@LAR3.917.4
15010Sun13-Nov-224:25PMDAL@GB17.42.7
15110Sun13-Nov-228:20PMLAC@SF9.43.1
15210Mon14-Nov-228:15PMWAS@PHI3.211.4
15311Thu17-Nov-228:15PMTEN@GB253.9
15411Sun20-Nov-221:00PMCAR@BAL5.414.6
15511Sun20-Nov-221:00PMCHI@ATL6.510
15611Sun20-Nov-221:00PMCLE@BUF11.814
15711Sun20-Nov-221:00PMDET@NYG44
15811Sun20-Nov-221:00PMNYJ@NE3.511.5
15911Sun20-Nov-221:00PMPHI@IND1.75
16011Sun20-Nov-221:00PMLAR@NO8.525.46
16111Sun20-Nov-221:00PMWAS@HOU11.54.7
16211Sun20-Nov-224:05PMLVR@DEN4.310.6
16311Sun20-Nov-224:25PMDAL@MIN9.510.2
16411Sun20-Nov-224:25PMKC@LAC46.11.6
16511Sun20-Nov-228:20PMCIN@PIT4.818.7
16611Mon21-Nov-228:15PMSF@ARI24.47.1
Results
 
Upvote 0
Thanks for that, I'll have a look in the morning, if no-one else has answered.
 
Upvote 0
Ok, how about
Fluff.xlsm
ABCDEFGHIJKLMNOPQR
1WeekAwayHomeLast3
2WeekDayDateTimeAwayHomeTETETeamNO60.28
31Thu448128:20PMBUF@LAR1.58.9ARI
41Sun448151:00PMNO@ATL21.65.4ATL
51Sun448151:00PMPHI@DET97.8BAL
61Sun448151:00PMJAX@WAS6.810.8BUF
71Sun448151:00PMIND@HOU9.822CAR
81Sun448151:00PMPIT@CIN18.510.8CHI
91Sun448151:00PMBAL@NYJ10.211.4CIN
101Sun448151:00PMSF@CHI40CLE
111Sun448151:00PMNE@MIA10.33.5DAL
121Sun448151:00PMCLE@CAR5.58.4DEN
131Sun448154:25PMNYG@TEN7.18.2DET
141Sun448154:25PMLVR@LAC11.919.8GB
151Sun448154:25PMKC@ARI35.211.4HOU
161Sun448154:25PMGB@MIN12.75.9IND
171Sun448158:20PMTB@DAL1.713.2JAX
181Mon448168:20PMDEN@SEA15.530.2KC
192Thu448198:15PMLAC@KC13.113.3LAC
202Sun448221:00PMWAS@DET16.29.1LAR
212Sun448221:00PMMIA@BAL15.634LVR
222Sun448221:00PMTB@NO3.39.4MIA
232Sun448221:00PMNE@PIT012.2MIN
242Sun448221:00PMIND@JAX5.313.4NE
252Sun448221:00PMNYJ@CLE1013.7NO
262Sun448221:00PMCAR@NYG4.68.6NYG
272Sun448224:05PMSEA@SF5.110.8NYJ
282Sun448224:05PMATL@LAR5.414.1PHI
292Sun448224:25PMHOU@DEN9.49.1PIT
302Sun448224:25PMARI@LVR16.723SEA
312Sun448224:25PMCIN@DAL9.31.8SF
322Sun448228:20PMCHI@GB2.83.1TB
332Mon448237:15PMTEN@BUF2.99.7TEN
342Mon448238:30PMMIN@PHI19.413.2WAS
353Thu448268:15PMPIT@CLE6.123.9
1379Sun448718:20PMTEN@KC11.228.1
1389Mon448728:15PMBAL@NO12.315.12
13910Thu448758:15PMATL@CAR6.40
14010Sun448789:30AMSEA@TB12.98.3
14110Sun448781:00PMCLE@MIA13.85.1
14210Sun448781:00PMDEN@TEN4.314.2
14310Sun448781:00PMDET@CHI8.623.4
14410Sun448781:00PMHOU@NYG10.214.3
14510Sun448781:00PMJAX@KC6.229.7
14610Sun448781:00PMMIN@BUF11.59.7
14710Sun448781:00PMNO@PIT19.710.5
14810Sun448784:05PMIND@LVR9.713.3
14910Sun448784:25PMARI@LAR3.917.4
15010Sun448784:25PMDAL@GB17.42.7
15110Sun448788:20PMLAC@SF9.43.1
15210Mon448798:15PMWAS@PHI3.211.4
15311Thu448828:15PMTEN@GB253.9
15411Sun448851:00PMCAR@BAL5.414.6
15511Sun448851:00PMCHI@ATL6.510
15611Sun448851:00PMCLE@BUF11.814
15711Sun448851:00PMDET@NYG44
15811Sun448851:00PMNYJ@NE3.511.5
15911Sun448851:00PMPHI@IND1.75
16011Sun448851:00PMLAR@NO8.525.46
16111Sun448851:00PMWAS@HOU11.54.7
16211Sun448854:05PMLVR@DEN4.310.6
16311Sun448854:25PMDAL@MIN9.510.2
16411Sun448854:25PMKC@LAC46.11.6
16511Sun448858:20PMCIN@PIT4.818.7
16611Mon448868:15PMSF@ARI24.47.1
Main
Cell Formulas
RangeFormula
R2R2=LET(h,FILTER(C3:K1000,E3:E1000=Q2),a,FILTER(C3:M1000,H3:H1000=Q2),r,ROWS(h),s,SEQUENCE(r+ROWS(a)),SUM(INDEX(SORT(IF(s<=r,INDEX(h,s,{1,9}),INDEX(a,s-r,{1,11})),1,-1),{1;2;3},2)))
 
Upvote 0
Fluff said:
Ok, how about
Fluff.xlsm
ABCDEFGHIJKLMNOPQR
1WeekAwayHomeLast3
2WeekDayDateTimeAwayHomeTETETeamNO60.28
31Thu448128:20PMBUF@LAR1.58.9ARI
41Sun448151:00PMNO@ATL21.65.4ATL
51Sun448151:00PMPHI@DET97.8BAL
61Sun448151:00PMJAX@WAS6.810.8BUF
71Sun448151:00PMIND@HOU9.822CAR
81Sun448151:00PMPIT@CIN18.510.8CHI
91Sun448151:00PMBAL@NYJ10.211.4CIN
101Sun448151:00PMSF@CHI40CLE
111Sun448151:00PMNE@MIA10.33.5DAL
121Sun448151:00PMCLE@CAR5.58.4DEN
131Sun448154:25PMNYG@TEN7.18.2DET
141Sun448154:25PMLVR@LAC11.919.8GB
151Sun448154:25PMKC@ARI35.211.4HOU
161Sun448154:25PMGB@MIN12.75.9IND
171Sun448158:20PMTB@DAL1.713.2JAX
181Mon448168:20PMDEN@SEA15.530.2KC
192Thu448198:15PMLAC@KC13.113.3LAC
202Sun448221:00PMWAS@DET16.29.1LAR
212Sun448221:00PMMIA@BAL15.634LVR
222Sun448221:00PMTB@NO3.39.4MIA
232Sun448221:00PMNE@PIT012.2MIN
242Sun448221:00PMIND@JAX5.313.4NE
252Sun448221:00PMNYJ@CLE1013.7NO
262Sun448221:00PMCAR@NYG4.68.6NYG
272Sun448224:05PMSEA@SF5.110.8NYJ
282Sun448224:05PMATL@LAR5.414.1PHI
292Sun448224:25PMHOU@DEN9.49.1PIT
302Sun448224:25PMARI@LVR16.723SEA
312Sun448224:25PMCIN@DAL9.31.8SF
322Sun448228:20PMCHI@GB2.83.1TB
332Mon448237:15PMTEN@BUF2.99.7TEN
342Mon448238:30PMMIN@PHI19.413.2WAS
353Thu448268:15PMPIT@CLE6.123.9
1379Sun448718:20PMTEN@KC11.228.1
1389Mon448728:15PMBAL@NO12.315.12
13910Thu448758:15PMATL@CAR6.40
14010Sun448789:30AMSEA@TB12.98.3
14110Sun448781:00PMCLE@MIA13.85.1
14210Sun448781:00PMDEN@TEN4.314.2
14310Sun448781:00PMDET@CHI8.623.4
14410Sun448781:00PMHOU@NYG10.214.3
14510Sun448781:00PMJAX@KC6.229.7
14610Sun448781:00PMMIN@BUF11.59.7
14710Sun448781:00PMNO@PIT19.710.5
14810Sun448784:05PMIND@LVR9.713.3
14910Sun448784:25PMARI@LAR3.917.4
15010Sun448784:25PMDAL@GB17.42.7
15110Sun448788:20PMLAC@SF9.43.1
15210Mon448798:15PMWAS@PHI3.211.4
15311Thu448828:15PMTEN@GB253.9
15411Sun448851:00PMCAR@BAL5.414.6
15511Sun448851:00PMCHI@ATL6.510
15611Sun448851:00PMCLE@BUF11.814
15711Sun448851:00PMDET@NYG44
15811Sun448851:00PMNYJ@NE3.511.5
15911Sun448851:00PMPHI@IND1.75
16011Sun448851:00PMLAR@NO8.525.46
16111Sun448851:00PMWAS@HOU11.54.7
16211Sun448854:05PMLVR@DEN4.310.6
16311Sun448854:25PMDAL@MIN9.510.2
16411Sun448854:25PMKC@LAC46.11.6
16511Sun448858:20PMCIN@PIT4.818.7
16611Mon448868:15PMSF@ARI24.47.1
Main
Cell Formulas
RangeFormula
R2R2=LET(h,FILTER(C3:K1000,E3:E1000=Q2),a,FILTER(C3:M1000,H3:H1000=Q2),r,ROWS(h),s,SEQUENCE(r+ROWS(a)),SUM(INDEX(SORT(IF(s<=r,INDEX(h,s,{1,9}),INDEX(a,s-r,{1,11})),1,-1),{1;2;3},2)))
Click to expand...

I'm afraid that isn't working for me. Perhaps I'm doing it wrong.

Here's the full sheet.

Cell Y3 should be the last 3 TE scores for ARI.

(Really sorry for the hassle)

Mr Excel.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Away TeamHome TeamLast 3 Pts Scored
2WeekDayDateTimeAwayHomeTEWRQBRBDSTOff. TotalTEWRQBRBDSTOff. TotalTEWRQBRBDSTOff. Total
31Thu08-Sep-228:20PMBUF@LAR1.560.731.481417107.688.942.87.812.3971.8ARI#REF!
41Sun11-Sep-221:00PMNO@ATL21.651.621.668.98103.765.426.119.825.3676.6ATL
51Sun11-Sep-221:00PMPHI@DET927.224.7236.7597.627.839.415.542.50105.2BAL
61Sun11-Sep-221:00PMJAX@WAS6.840.213.428.4688.810.85127.7227.84117.32BUF
71Sun11-Sep-221:00PMIND@HOU9.84715.2838.96110.982225.915.516.9780.3CAR
81Sun11-Sep-221:00PMPIT@CIN18.523.212.6611.32665.6610.846.918.2228.22104.12CHI
91Sun11-Sep-221:00PMBAL@NYJ10.235.320.224.21169.9211.441.514.2827.1494.28CIN
101Sun11-Sep-221:00PMSF@CHI432.59.969.1555.56025.913.6419.31058.84CLE
111Sun11-Sep-221:00PMNE@MIA10.3218.5212.5452.323.542.314.816.71877.3DAL
121Sun11-Sep-221:00PMCLE@CAR5.521.610.8838.3676.288.431.41816.5174.3DEN
131Sun11-Sep-224:25PMNYG@TEN7.134.216.0235.8493.128.226.919.3430.1984.54DET
141Sun11-Sep-224:25PMLVR@LAC11.937.813.820.2083.719.832.923.2625.513101.46GB
151Sun11-Sep-224:25PMKC@ARI35.234.634.942.53147.211.432.923.125.6-293HOU
161Sun11-Sep-224:25PMGB@MIN12.723.16.330.7172.85.950.419.0817.41292.78IND
171Sun11-Sep-228:20PMTB@DAL1.73910.3819.81370.8813.223.57.0210.1553.82JAX
181Mon12-Sep-228:20PMDEN@SEA15.531.417.828.2592.930.219.617.28.9775.9KC
192Thu15-Sep-228:15PMLAC@KC13.146.823.46291112.3613.327.817.3261084.4LAC
202Sun18-Sep-221:00PMWAS@DET16.254.627.7825.42123.989.152.226.0427.69114.94LAR
212Sun18-Sep-221:00PMMIA@BAL15.68538.8622.5-1161.963434.942.6227113.52LVR
222Sun18-Sep-221:00PMTB@NO3.337.69.49.52659.89.437.97.6412.1467.04MIA
232Sun18-Sep-221:00PMNE@PIT045.112.6822.8880.5812.223.411.4216.4363.42MIN
242Sun18-Sep-221:00PMIND@JAX5.320.91.814.7042.713.437.518.524.12193.5NE
252Sun18-Sep-221:00PMNYJ@CLE1057.726.8824.42118.9813.726.715.4641.7397.56NO
262Sun18-Sep-221:00PMCAR@NYG4.618.713.315.7452.38.628.213.1412.8762.74NYG
272Sun18-Sep-224:05PMSEA@SF5.128.86.0811.21151.1810.83019.1621.71081.66NYJ
282Sun18-Sep-224:05PMATL@LAR5.443.613.448.71871.1414.149.718.8818.99101.58PHI
292Sun18-Sep-224:25PMHOU@DEN9.422.67.1811.6650.789.128.611.0615.8764.56PIT
302Sun18-Sep-224:25PMARI@LVR16.734.925.8829.49106.882331.718.9812.6386.28SEA
312Sun18-Sep-224:25PMCIN@DAL9.335.616.5613.3474.761.835.513.625.7776.6SF
322Sun18-Sep-228:20PMCHI@GB2.86.58.820.7538.83.136.916.3639.7996.06TB
332Mon19-Sep-227:15PMTEN@BUF2.918.40.7213.2-135.229.760.129.8811.120110.78TEN
342Mon19-Sep-228:30PMMIN@PHI19.427.98.8412768.1413.245.233.0217.512108.92WAS
353Thu22-Sep-228:15PMPIT@CLE6.130.914.9817.4269.3823.926.717.926.41094.9
363Sun25-Sep-221:00PMBUF@MIA10.747.626.738.38123.36.921.911.4416.8757.04
373Sun25-Sep-221:00PMCIN@NYJ3.647.52322.31896.417.925.43.420.8467.5
383Sun25-Sep-221:00PMDET@MIN10.837.913.8835.1297.6813.94218.325499.2
393Sun25-Sep-221:00PMHOU@CHI6.122.19.825.11163.1613.74.9434.3658.94
403Sun25-Sep-221:00PMKC@IND21.521.917.0817.81078.2819.92915.2820.1684.28
413Sun25-Sep-221:00PMNO@CAR3.459.714.6215.1492.82526.110.7161657.8
423Sun25-Sep-221:00PMPHI@WAS1855.227.6620106.82.534.58.6421.6567.24
433Sun25-Sep-221:00PMLVR@TEN12.644.1619.8218.8395.3814.822.618.7630.7386.86
443Sun25-Sep-221:00PMBAL@NE39.516.239.421211107.128.334.515.9432.7691.44
453Sun25-Sep-224:05PMJAX@LAC3.959.725.0831.19119.784.538.412.5819.7-475.18
463Sun25-Sep-224:25PMATL@SEA13.721.315.5627.2477.7625.541.618.920.77106.7
473Sun25-Sep-224:25PMGB@TB14.343.516.111.51185.412.43514.7412674.14
483Sun25-Sep-224:25PMLAR@ARI10.13710.1611.8669.0610.547.113.3613.7484.66
493Sun25-Sep-228:20PMSF@DEN6.829.48.4415.71060.342.221.49.0625.51658.16
503Mon26-Sep-228:15PMDAL@NYG10.635.312.4251083.39.621.913.7422.9068.14
514Thu29-Sep-228:15PMMIA@CIN4.341.313.2220.1178.9214.545.1220.0817.4897.1
524Sun02-Oct-229:30AMMIN@NO5.35012.9219.4687.6221.130.513.747.3472.64
534Sun02-Oct-221:00PMBUF@BAL730.623.5212.2773.323.516.813.0630563.36
544Sun02-Oct-221:00PMCHI@NYG7.912.310.1613.9644.268.13.523.08201654.68
554Sun02-Oct-221:00PMCLE@ATL15.519.514.9629.5379.466.711.23.8634.9656.66
564Sun02-Oct-221:00PMJAX@PHI7.229.35.6610.3952.4612.219.215.9640.21587.56
574Sun02-Oct-221:00PMNYJ@PIT8.238.6818.7820.71286.3616.720.413.7610.6761.46
584Sun02-Oct-221:00PMTEN@IND14.216.814.3829.51074.884130.217.845.6394.64
594Sun02-Oct-221:00PMLAC@HOU18.53121.142.18112.71029.913.8440.8294.54
604Sun02-Oct-221:00PMSEA@DET25.937.631.732.17127.339.938.133.2237.8-2149.02
614Sun02-Oct-221:00PMWAS@DAL12.826.27.322.73691.741.217.6211.91072.42
624Sun02-Oct-224:05PMARI@CAR22.126.122.8818.11289.18817.75.88261157.58
634Sun02-Oct-224:25PMDEN@LVR3.545.327.486.4282.685.425.811.5237.81180.52
644Sun02-Oct-224:25PMNE@GB3.325.77.4428.31164.7410.247.816.1423.9798.04
654Sun02-Oct-228:20PMKC@TB38.325.423.3629.74116.761649.325.4331123.7
664Mon03-Oct-228:15PMLAR@SF1935.36.768.2169.264.437.213.8618.82374.26
675Thu06-Oct-228:15PMIND@DEN4.3346.1423.31867.7410.123.69.1624.81467.66
685Sun09-Oct-229:30AMNYG@GB11.820.712.3830.8375.6818.137.116.8813.4585.48
695Sun09-Oct-221:00PMATL@TB429.717.9818069.6810.337.919.7443.16111.04
705Sun09-Oct-221:00PMCHI@MIN9.62017.0219.3665.9210.853.2222.2433.14119.36
715Sun09-Oct-221:00PMDET@NE1.630.55.8619.2257.169.427.810.0221.72268.92
725Sun09-Oct-221:00PMHOU@JAX4.216.55.825.11151.619.42410.3420.9574.64
735Sun09-Oct-221:00PMMIA@NYJ11.923.82.6425.6-263.943.224.114.645986.9
745Sun09-Oct-221:00PMTEN@WAS3.218.611.5439.5672.846.95121.8617.8597.56
755Sun09-Oct-221:00PMPIT@BUF15.737.412.0816.9482.084.980.935.2815.414136.48
765Sun09-Oct-221:00PMLAC@CLE1.231.714.4252.4199.7214.831.214.440.10100.5
775Sun09-Oct-221:00PMSEA@NO1245.224.0222.21103.4247.5819.99.8827.24104.56
785Sun09-Oct-224:05PMSF@CAR7.73317.9224.91583.522.832.8929.31173.9
795Sun09-Oct-224:25PMDAL@LAR020.23.9822.42546.5811.646.710.3210.3478.92
805Sun09-Oct-224:25PMPHI@ARI17.529.827.6610.4485.3610.839.916.222.5389.4
815Sun09-Oct-228:20PMCIN@BAL17.322.517.2821.4478.4825.618.114.764.7663.16
825Mon10-Oct-228:15PMLVR@KC4.933.917.9437.5294.2435.238.830.4815.71120.18
Results
Cell Formulas
RangeFormula
Y3Y3=LET(h,FILTER(C3:I274,E3:E274=X3),a,FILTER(C3:P274,G3:G274=X3),r,ROWS(h),s,SEQUENCE(r+ROWS(a)),SUM(INDEX(SORT(IF(s<=r,INDEX(h,s,{1,9}),INDEX(a,s-r,{1,11})),1,-1),{1;2;3},2)))
 
Upvote 0
That's because you have changed the columns, but the reference to them in the two Index functions at the end.
It should be
Excel Formula: 
=LET(h,FILTER(C3:I274,E3:E274=X3),a,FILTER(C3:P274,G3:G274=X3),r,ROWS(h),s,SEQUENCE(r+ROWS(a)),SUM(INDEX(SORT(IF(s<=r,INDEX(h,s,{1,7}),INDEX(a,s-r,{1,14})),1,-1),{1;2;3},2)))
 
Upvote 0
a
Fluff said:
That's because you have changed the columns, but the reference to them in the two Index functions at the end.
It should be
Excel Formula: 
=LET(h,FILTER(C3:I274,E3:E274=X3),a,FILTER(C3:P274,G3:G274=X3),r,ROWS(h),s,SEQUENCE(r+ROWS(a)),SUM(INDEX(SORT(IF(s<=r,INDEX(h,s,{1,7}),INDEX(a,s-r,{1,14})),1,-1),{1;2;3},2)))
Click to expand...
Ah OK.

Just C&P'd your formula into my spready and the result is returning '0', but it should be '43.4'
 
Upvote 0
It returns 43.4 for me
Fluff.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Away TeamHome TeamLast 3 Pts Scored
2WeekDayDateTimeAwayHomeTEWRQBRBDSTOff. TotalTEWRQBRBDSTOff. TotalTEWRQBRBDSTOff. Total
31Thu448128:20PMBUF@LAR1.560.731.51417107.688.942.87.812.3971.8ARI43.4
41Sun448151:00PMNO@ATL21.651.621.78.98103.765.426.119.825.3676.6ATL24.4
51Sun448151:00PMPHI@DET927.224.736.7597.627.839.415.542.50105.2BAL68.6
61Sun448151:00PMJAX@WAS6.840.213.428.4688.810.85127.727.84117.32BUF22.6
71Sun448151:00PMIND@HOU9.84715.338.96110.982225.915.516.9780.3CAR15.8
81Sun448151:00PMPIT@CIN18.523.212.711.32665.6610.846.918.228.22104.12CHI23.5
91Sun448151:00PMBAL@NYJ10.235.320.24.21169.9211.441.514.327.1494.28CIN35.4
101Sun448151:00PMSF@CHI432.59.969.1555.56025.913.619.31058.84CLE54.2
111Sun448151:00PMNE@MIA10.3218.5212.5452.323.542.314.816.71877.3DAL12.3
121Sun448151:00PMCLE@CAR5.521.610.938.3676.288.431.41816.5174.3DEN15.8
131Sun448154:25PMNYG@TEN7.134.21635.8493.128.226.919.330.1984.54DET52.3
141Sun448154:25PMLVR@LAC11.937.813.820.2083.719.832.923.325.513101.46GB42.6
151Sun448154:25PMKC@ARI35.234.634.942.53147.211.432.923.125.6-293HOU20.3
161Sun448154:25PMGB@MIN12.723.16.330.7172.85.950.419.117.41292.78IND65.2
171Sun448158:20PMTB@DAL1.73910.419.81370.8813.223.57.0210.1553.82JAX30.5
181Mon448168:20PMDEN@SEA15.531.417.828.2592.930.219.617.28.9775.9KC95
192Thu448198:15PMLAC@KC13.146.823.5291112.3613.327.817.3261084.4LAC24.2
202Sun448221:00PMWAS@DET16.254.627.825.42123.989.152.22627.69114.94LAR40.7
212Sun448221:00PMMIA@BAL15.68538.922.5-1161.963434.942.627113.52LVR22.9
222Sun448221:00PMTB@NO3.337.69.49.52659.89.437.97.6412.1467.04MIA23.1
232Sun448221:00PMNE@PIT045.112.722.8880.5812.223.411.416.4363.42MIN30
242Sun448221:00PMIND@JAX5.320.91.814.7042.713.437.518.524.12193.5NE21
252Sun448221:00PMNYJ@CLE1057.726.924.42118.9813.726.715.541.7397.56NO72.08
262Sun448221:00PMCAR@NYG4.618.713.315.7452.38.628.213.112.8762.74NYG29.5
272Sun448224:05PMSEA@SF5.128.86.0811.21151.1810.83019.221.71081.66NYJ29.3
282Sun448224:05PMATL@LAR5.443.613.48.71871.1414.149.718.918.99101.58PHI47.7
292Sun448224:25PMHOU@DEN9.422.67.1811.6650.789.128.611.115.8764.56PIT38.5
302Sun448224:25PMARI@LVR16.734.925.929.49106.882331.71912.6386.28SEA63.4
312Sun448224:25PMCIN@DAL9.335.616.613.3474.761.835.513.625.7776.6SF18.9
322Sun448228:20PMCHI@GB2.86.58.820.7538.83.136.916.439.7996.06TB38.7
332Mon448237:15PMTEN@BUF2.918.40.7213.2-135.229.760.129.911.120110.78TEN32.2
342Mon448238:30PMMIN@PHI19.427.98.8412768.1413.245.23317.512108.92WAS22.2
353Thu448268:15PMPIT@CLE6.130.91517.4269.3823.926.717.926.41094.9
363Sun448291:00PMBUF@MIA10.747.626.738.38123.36.921.911.416.8757.04
373Sun448291:00PMCIN@NYJ3.647.52322.31896.417.925.43.420.8467.5
383Sun448291:00PMDET@MIN10.837.913.935.1297.6813.94218.325499.2
393Sun448291:00PMHOU@CHI6.122.19.825.11163.1613.74.9434.3658.94
403Sun448291:00PMKC@IND21.521.917.117.81078.2819.92915.320.1684.28
413Sun448291:00PMNO@CAR3.459.714.615.1492.82526.110.7161657.8
423Sun448291:00PMPHI@WAS1855.227.6620106.82.534.58.6421.6567.24
433Sun448291:00PMLVR@TEN12.644.219.818.8395.3814.822.618.830.7386.86
443Sun448291:00PMBAL@NE39.516.239.41211107.128.334.515.932.7691.44
453Sun448294:05PMJAX@LAC3.959.725.131.19119.784.538.412.619.7-475.18
463Sun448294:25PMATL@SEA13.721.315.627.2477.7625.541.618.920.77106.7
473Sun448294:25PMGB@TB14.343.516.111.51185.412.43514.712674.14
483Sun448294:25PMLAR@ARI10.13710.211.8669.0610.547.113.413.7484.66
493Sun448298:20PMSF@DEN6.829.48.4415.71060.342.221.49.0625.51658.16
503Mon448308:15PMDAL@NYG10.635.312.4251083.39.621.913.722.9068.14
514Thu448338:15PMMIA@CIN4.341.313.220.1178.9214.545.120.117.4897.1
524Sun448369:30AMMIN@NO5.35012.919.4687.6221.130.513.77.3472.64
534Sun448361:00PMBUF@BAL730.623.512.2773.323.516.813.130563.36
544Sun448361:00PMCHI@NYG7.912.310.213.9644.268.13.523.1201654.68
554Sun448361:00PMCLE@ATL15.519.51529.5379.466.711.23.8634.9656.66
564Sun448361:00PMJAX@PHI7.229.35.6610.3952.4612.219.21640.21587.56
574Sun448361:00PMNYJ@PIT8.238.718.820.71286.3616.720.413.810.6761.46
584Sun448361:00PMTEN@IND14.216.814.429.51074.884130.217.85.6394.64
594Sun448361:00PMLAC@HOU18.53121.142.18112.71029.913.840.8294.54
604Sun448361:00PMSEA@DET25.937.631.732.17127.339.938.133.237.8-2149.02
614Sun448361:00PMWAS@DAL12.826.27.322.73691.741.217.611.91072.42
624Sun448364:05PMARI@CAR22.126.122.918.11289.18817.75.88261157.58
634Sun448364:25PMDEN@LVR3.545.327.56.4282.685.425.811.537.81180.52
644Sun448364:25PMNE@GB3.325.77.4428.31164.7410.247.816.123.9798.04
654Sun448368:20PMKC@TB38.325.423.429.74116.761649.325.4331123.7
664Mon448378:15PMLAR@SF1935.36.768.2169.264.437.213.918.82374.26
675Thu448408:15PMIND@DEN4.3346.1423.31867.7410.123.69.1624.81467.66
685Sun448439:30AMNYG@GB11.820.712.430.8375.6818.137.116.913.4585.48
695Sun448431:00PMATL@TB429.71818069.6810.337.919.743.16111.04
705Sun448431:00PMCHI@MIN9.6201719.3665.9210.853.222.233.14119.36
715Sun448431:00PMDET@NE1.630.55.8619.2257.169.427.81021.72268.92
725Sun448431:00PMHOU@JAX4.216.55.825.11151.619.42410.320.9574.64
735Sun448431:00PMMIA@NYJ11.923.82.6425.6-263.943.224.114.645986.9
745Sun448431:00PMTEN@WAS3.218.611.539.5672.846.95121.917.8597.56
755Sun448431:00PMPIT@BUF15.737.412.116.9482.084.980.935.315.414136.48
765Sun448431:00PMLAC@CLE1.231.714.452.4199.7214.831.214.440.10100.5
775Sun448431:00PMSEA@NO1245.22422.21103.4247.619.99.8827.24104.56
785Sun448434:05PMSF@CAR7.73317.924.91583.522.832.8929.31173.9
795Sun448434:25PMDAL@LAR020.23.9822.42546.5811.646.710.310.3478.92
805Sun448434:25PMPHI@ARI17.529.827.710.4485.3610.839.916.222.5389.4
815Sun448438:20PMCIN@BAL17.322.517.321.4478.4825.618.114.84.7663.16
825Mon448448:15PMLVR@KC4.933.917.937.5294.2435.238.830.515.71120.18
Main
Cell Formulas
RangeFormula
Y3:Y34Y3=LET(h,FILTER($C$3:$I$274,$E$3:$E$274=X3),a,FILTER($C$3:$P$274,$G$3:$G$274=X3),r,ROWS(h),s,SEQUENCE(r+ROWS(a)),SUM(INDEX(SORT(IF(s<=r,INDEX(h,s,{1,7}),INDEX(a,s-r,{1,14})),1,-1),{1;2;3},2)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
174
Peter_SSs
Peter_SSs
E
  • Solved
Summing Values
Replies
3
Views
269
LazyBug
LazyBug
Z
  • Question
Sum Sales Values by Dates over different periods
Replies
6
Views
133
RobP
R
L
  • Solved
IF Statement Added to VBA Code to Display Blank if Sum Equals 0
2
Replies
10
Views
406
Joe4
Joe4
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
197
Alex Blakenburg
A

Forum statistics

Threads
1,215,771
Messages
6,126,797
Members
449,337
Latest member
BBV123

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top