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(Σ))))))))
- Adding text with Tick Box in a single cell in Google Sheets
- Grabbing a Chart as image via JS script and Google Sheets API?
- Counting the previous sample over multiple conditions throughout the range in Google Sheets
- Get BTC prices on Google Sheets from Google Finance
- ArrayFormula, lookup and filter using multiple criteria
- How to extract URL from Link in Google Sheets using a formula?
- Google Sheets SUM until a condition fails
- Cannot sort table by column using Google Sheets API in C#
- How to debug a spreadsheet custom function in google script?
- How to add background color and a number in sparkline cell?
- Output rows from a range by several conditions in Google Sheets
- Can't convert cell values to uppercase using UPPER function
- App script web change version for each deploy
- Can't use SUM inside ARRAYFORMULA
- How to sum up to a specific value in Google Sheets
- Create connected drop-downs in google sheets where value can be present only in one drop-down
- Google Sheets App Script: Trigger on a copied sheet
- Convert each name and corresponding data into separate columns
- Extract randomly data according to multiple criteria
- Google Sheets Count Unique Values
- How to add prefix and suffix text to to part of text in an arrayformula
- Google Apps Script, Google Sheets - Getting Spreadsheet ID and Sheet ID programmatically
- Jump From Cell A to Cell B in Google Sheets without hardcoding
- Is there a way to copy from a Google Sheets that is copy locked?
- How to import data from multiple sheets into one sheet with BYROW and LAMBDA?
- How do I pick a random cell in Google Sheets based on the cell next to the target list of data?
- Google Sheets to Calendar - something wrong with the syntax of the date
- Match data based on id between sheets of one googlesheet &extract alphanumeric values in separate column
- Randomizing the contents of a list in Google Sheets
- How to add "today" to date validation via Apps Script?