Counting the previous sample over multiple conditions throughout the range

Question

How to use 1 formula to calculate the number of points (column E) in the previous matches of the team with similar level opponents (columns F and G). Similar evel of opponents is If the opponent's place in the current cell is <=9, then calculating matches against teams with a place <=9, if more, then with a place >9.

For example, for team "Sokol Miedzychod" in row 25, the opponent's place =7, so the formula should calculate the points in the previous matches involving this team (which are above the current cell) against teams whose place is <=9. Next to columns J-K I have manually made a sample with simple formulas of what should be obtained.

I think map + lambda + sumifs will work, but I don't know exactly what the formula is supposed to look like.

Link on file: https://docs.google.com/spreadsheets/d/116RdcWKRCkxjNxr2ETkbwSHleuwPgTMfO9Lst6Cx-Lg/edit?gid=0#gid=0

Answer 1

You may try:

=map(C2:C,D2:D,E2:E,F2:F,G2:G,lambda(c,d,e,f,g,if(c="",,let(Λ,lambda(x,offset(x,-1,)),Δ,lambda(x,index(sumifs(E1:Λ(e),C1:Λ(c),c,lte(G1:Λ(g),A2),x)+sumifs(E1:Λ(e),D1:Λ(d),c,lte(F1:Λ(f),A2),x))),
 if(countif(C1:offset(c,,1),c)=1,"-",if(lte(g,A2),Δ(true),Δ(false)))))))