simplify a vlookup formula

[Kerryx]

Hi just wondering if anyone would have any ideas on simplifying this formula

=IF(Leaderboard!$C88="","",SUM(VLOOKUP(Entries_by_Name!$C88,All_Football_Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP(Entries_by_Name!$D88,All_Football_Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP(Entries_by_Name!$E88,All_Football_Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP(Entries_by_Name!$F88,All_Football_Tables!$C$3:$Y$22,14,FALSE)+VLOOKUP(Entries_by_Name!$G88,All_Football_Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP(Entries_by_Name!$H88,All_Football_Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP(Entries_by_Name!$I88,All_Football_Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP(Entries_by_Name!$J88,All_Football_Tables!$C$25:$Y$48,14,FALSE)+VLOOKUP(Entries_by_Name!$K88,All_Football_Tables!$C$52:$Y$75,14,FALSE)+VLOOKUP(Entries_by_Name!$L88,All_Football_Tables!$C$52:$Y$75,14,FALSE)+VLOOKUP(Entries_by_Name!$M88,All_Football_Tables!$C$52:$Y$75,14,FALSE)+VLOOKUP(Entries_by_Name!$N88,All_Football_Tables!$C$78:$Y$101,14,FALSE)+VLOOKUP(Entries_by_Name!$O88,All_Football_Tables!$C$78:$Y$101,14,FALSE)+VLOOKUP(Entries_by_Name!$P88,All_Football_Tables!$C$78:$Y$101,14,FALSE)))

https://unsee.cc/7ed2f5b7/

 

[Special-K99]

Completely untested so test thoroughly

Does this work?

=IF(Leaderboard!$C88="","",
SUMPRODUCT((Entries_by_Name!$C88:$F88,All_Football_Tables!$C$3:$Y$22,14,FALSE))+
SUMPRODUCT((Entries_by_Name!$G88:$J88,All_Football_Tables!$C$25:$Y$48,14,FALSE))+
SUMPRODUCT((Entries_by_Name!$K88:$M88,All_Football_Tables!$C$52:$Y$75,14,FALSE))+
SUMPRODUCT((Entries_by_Name!$N88:$P88,All_Football_Tables!$C$78:$Y$101,14,FALSE)))

 

[Kerryx]

no doesn't seem to work getting a Value error

 

[Special-K99]

There's prob text in the ranges, SUMPRODUCT doesnt like a range that consists of numbers and text.

 

[Kerryx]

Correct, the Entries by names sheet contains the teams selected by each entry and the All Football Tables sheet contains the leage tables with the points got each month.

 

[Eric W]

How about:

=IF(Leaderboard!$C88="","",SUMPRODUCT(SUMIF(All_Football_Tables!$C$3:$C$22,Entries_By_Name!$C88:$P88,All_Football_Tables!$P$3:$P$22)))

 

[Kerryx]

Thanks Eric looks good

 

[Eric W]

You're welcome.