Fixing 1 row, and leaves each following row movable

Question

Now in the range L2:L16 formulas made manually are looking for the number of points of the team from the column “Home” in the previous matches (from the second row to the current one). Example of one of the 15 formulas:

=SUMIF(H2:H4,H4,J2:J4)+SUMIF(I2:I4,H4,K2:K4)

Is it possible to make 1 formula that fixes the second row, and leaves each following row movable to calculate the sum of points of the home teams?

Link on file: https://docs.google.com/spreadsheets/d/1Nka6nXBmdkBrPi9fGcB1cN_mJKVajumqHwxKBIj5Z4c/edit?gid=0#gid=0

Answer 1

You may try:

=map(H2:H,I2:I,J2:J,K2:K,lambda(Σ,Λ,Δ,Γ,if(Σ="",,sumif(H2:Σ,Σ,J2:Δ)+sumif(I2:Λ,Σ,K2:Γ))))