admin管理员组

文章数量:1298466

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: #gid=0

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

  • google-sheets
  • google-sheets-formula
Share Improve this question edited Jan 23 at 10:53 DarkBee 15.7k8 gold badges70 silver badges113 bronze badges asked Jan 23 at 10:52 TheGunner4TheGunner4 615 bronze badges 2
  • 1 Can you try this one? =SUM.IF(H$2:H2,H2,J$2:J2)+SUM.IF(I$2:I2,H2,K$2:K2) – Skizo-ozᴉʞS ツ Commented Jan 23 at 10:56
  • Thanks for the idea, but I need 1 formula that pulls through the whole array, like arrayformula or something like that – TheGunner4 Commented Jan 23 at 11:01
Add a comment  | 

2 Answers 2

Reset to default 1

You may try:

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

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.

本文标签： google sheetsFixing 1 rowand leaves each following row movableStack Overflow

版权声明：本文标题：google sheets - Fixing 1 row, and leaves each following row movable - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1738506407a2090560.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。