CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
TheGunner4
TheGunner4

Reputation: 61

Fixing 1 row, and leaves each following row movable

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

Upvotes: 1

Views: 56

Answers (2)

rockinfreakshow
rockinfreakshow

Reputation: 30240

You may try:

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

enter image description here

Upvotes: 1

Skizo-ozᴉʞS ツ
Skizo-ozᴉʞS ツ

Reputation: 20646

Try doing this

=SUM.IF(H$2:H2,H2,J$2:J2)+SUM.IF(I$2:I2,H2,K$2:K2)

Edit

If you want an Array you can use ARRAYFORMULA(https://support.google.com/docs/answer/3093275?hl=en):

=ARRAYFORMULA(IF(H2:H336<>"", 
    MMULT((ROW(H2:H336)>=TRANSPOSE(ROW(H2:H336)))*(H2:H336=TRANSPOSE(H2:H336)), J2:J336) + 
    MMULT((ROW(H2:H336)>=TRANSPOSE(ROW(H2:H336)))*(H2:H336=TRANSPOSE(I2:I336)), K2:K336), 
    ""))

Why it works?

This array formula calculates the cumulative points for (H2:H336) for all matches dynamically.

Upvotes: 1

Related Questions