Simplify Excel Calculation

[Kerryx]

Hi guys hoping someone may be able to help make my calculation more efficient , if possible.

Its for a soccer league ,
this one looks at the club entries by name from individual B18 and adds up the points for the club selected ( in column C18:F18 on Entries_by_Name sheet) from the sheet All_Footaball_Tables ( points are in the table in column 8)

=IF($B18="","",SUM(VLOOKUP(Entries_by_Name!C18,All_Football_Tables!$C$3:$N$22,8,FALSE)+VLOOKUP(Entries_by_Name!D18,All_Football_Tables!$C$3:$N$22,8,FALSE)+VLOOKUP(Entries_by_Name!E18,All_Football_Tables!$C$3:$N$22,8,FALSE)+VLOOKUP(Entries_by_Name!F18,All_Football_Tables!$C$3:$N$22,8,FALSE)))

We have 4 English leagues Premiership, Championship, Lg1 & Lg2
This one is used in the Leaderboard to calculate the monthly ( column 14) leader

=IF(Leaderboard!$B91="","",SUM(VLOOKUP(Entries_by_Name!$C91,All_Football_Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP(Entries_by_Name!$D91,All_Football_Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP(Entries_by_Name!$E91,All_Football_Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP(Entries_by_Name!$F91,All_Football_Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP(Entries_by_Name!$G91,All_Football_Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP(Entries_by_Name!$H91,All_Football_Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP(Entries_by_Name!$I91,All_Football_Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP(Entries_by_Name!$J91,All_Football_Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP(Entries_by_Name!$K91,All_Football_Tables!$C$52:$Y$75,14,FALSE)+VLOOKUP(Entries_by_Name!$L91,All_Football_Tables!$C$52:$Y$75,14,FALSE)+VLOOKUP(Entries_by_Name!$M91,All_Football_Tables!$C$52:$Y$75,14,FALSE)+VLOOKUP(Entries_by_Name!$N91,All_Football_Tables!$C$78:$Y$101,14,FALSE)+VLOOKUP(Entries_by_Name!$O91,All_Football_Tables!$C$78:$Y$101,14,FALSE)+VLOOKUP(Entries_by_Name!$P91,All_Football_Tables!$C$78:$Y$101,14,FALSE)))

Any help appreciated

 

[Kerryx]

Anyone??

 

[AliGW]

Are you expecting us to recreate the data from that formula? What you need to do is provide a sample table in your next post so that we can see how it relates.

 

[Kerryx]

Are you expecting us to recreate the data from that formula? What you need to do is provide a sample table in your next post so that we can see how it relates.

Apologies AliGW , see link below to working example, with sensitive data removed.

https://app.box.com/s/brlnniqk3v2dqx3u9n4tacpi80chz060

1. Entries are entered on Entries sheet by number
2. Your Teams uses vlookup to get team names from Teams_No's Sheet
3. Sheets Prem, Champ,Div1,Div2 contain match results and use the month column to calculate monthly points in the Tables Sheet.
4. Pts_Summary Sheet , Columns C,D,E,F look ate each of the teams selected in Your Teams Sheet and gets the relevant team points from column 8 in the Tables Sheet using this formula

=IF($B2="","",SUM(VLOOKUP('Your Teams'!C2,Tables!$C$3:$L$22,8,FALSE)+VLOOKUP('Your Teams'!D2,Tables!$C$3:$L$22,8,FALSE)+VLOOKUP('Your Teams'!E2,Tables!$C$3:$L$22,8,FALSE)+VLOOKUP('Your Teams'!F2,Tables!$C$3:$L$22,8,FALSE)))

5.In the Leaders sheet, for selecting a monthly leader this formula looks at the teams selected by each entrant in "Your Teams" and adds the points for that particular month e.g. Aug (in column 14) for each team selected in each particular league.

=IF(Leaders!$B3="","",SUM(VLOOKUP('Your Teams'!$C3,Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP('Your Teams'!$D3,Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP('Your Teams'!$E3,Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP('Your Teams'!$F3,Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP('Your Teams'!$G3,Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP('Your Teams'!$H3,Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP('Your Teams'!$I3,Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP('Your Teams'!$J3,Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP('Your Teams'!$K3,Tables!$C$52:$Y$75,14,FALSE)+VLOOKUP('Your Teams'!$L3,Tables!$C$52:$Y$75,14,FALSE)+VLOOKUP('Your Teams'!$M3,Tables!$C$52:$Y$75,14,FALSE)+VLOOKUP('Your Teams'!$N3,Tables!$C$78:$Y$101,14,FALSE)+VLOOKUP('Your Teams'!$O3,Tables!$C$78:$Y$101,14,FALSE)+VLOOKUP('Your Teams'!$P3,Tables!$C$78:$Y$101,14,FALSE)))

Hope all this makes sense....