google-sheetsgoogle-sheets-formula
Counting the previous sample over multiple conditions throughout the range in Google Sheets
Can you please tell me a formula that:
- Finds points (columns D-E) for all teams in the same league (column A) if the number of previous matches (columns F-G) equals the number of matches in each following cell of columns D-E.
- Within the resulting points table from the first paragraph, the formula outputs the team's place (the RANK formula might work). In columns I-J, I manually sampled what the result should be.
The example is for the Spartak Subotica team from the Adriatic League (row 55).
- Number of previous matches of this team = 3 (column F), the formula looks for all teams (columns B-C) from the same league (column A) and with the same number of previous matches (columns F-G).
- as a result a dataset is obtained, for ease of understanding I have presented it in the table below:
| Team | Place | Matches played | Points |
|---|---|---|---|
| Borac Cacak | 8 | 3 | 2 |
| Buducnost | 1 | 3 | 6 |
| Dubai | 4 | 3 | 4 |
| Zadar | 8 | 3 | 2 |
| Igokea | 4 | 3 | 4 |
| KRKA | 15 | 3 | 0 |
| Mega | 1 | 3 | 6 |
| Mornar Bar | 15 | 3 | 0 |
| Partizan Belgrade | 1 | 3 | 6 |
| Cedevita Olympia | 8 | 3 | 2 |
| SC Derby | 4 | 3 | 4 |
| Spartak Subotica | 8 | 3 | 2 |
| Split | 8 | 3 | 2 |
| FMP Belgrade | 4 | 3 | 4 |
| Cibona | 8 | 3 | 2 |
| Red Star | 8 | 3 | 2 |
- The formula outputs the place (or rank) of the team in cell 55 (“Spartak Subotica”) within the resulting data set - 8th place. It is desirable to use 1 automated formula for all cells (like arrayformula or map+lambda)
File link: https://docs.google.com/spreadsheets/d/1ggW8GE3nwflw-_OY8FmAM_xxq2h80QX-B6jcgyon9aI/edit?gid=0#gid=0
Solution
Here's a generalized approach (for HOME) which you may adapt accordingly:
=map(A4:A,lambda(Σ,if(or(Σ="",index(F:F,row(Σ))=0),,let(Δ,{{A:A,B:B,D:D,F:F};{A:A,C:C,E:E,G:G}},Λ,lambda(x,choosecols(Δ,x)),
rank(index(D:D,row(Σ)),filter(Λ(3),Λ(1)=Σ,Λ(4)=index(F:F,row(Σ))))))))
