I am running into some issues when trying to take a table of games, and output a sorted list of matches played and points earned using the standard win=3 points, draw=1 point, loss=0 points.

Quick issue: If I take the simple conversion in =IF(B2=$A2,3,IF(B2=B$1,1,0)) and try to expand it to an arrayformula to get an intended result of {0,1,3,3} =ArrayFormula(IF(B2:R2=$A2,3,IF(B2:R2=B$1,1,0))) it only evaluates the first if condition and outputs {0,0,3,3}.

I have not found a good way to sum the values across each row iteratively, and include logic for pointing like above. Using SumIf or a Query it wants to evaluate the entire range for each criterion: ={unique(A2:A5),ARRAYFORMULA(sumif(B2:E5,unique(A2:A5),G2:J5))}

I can work around this by creating a filtered list ={"Player";unique(filter(A2:A5,NOT(ISBLANK(A2:A5))))}

Then account for it checking the entire section instead of going row by row with some quick math ={"Played","Points";ARRAYFORMULA(COUNTIF(B2:E5,A2:A5)/2),ARRAYFORMULA(COUNTIF(B2:E5,A2:A5)*1.5)} but this still leaves us unable to count the number of games played accurately and with no options for a draw in the points totals.

The intended output would be:

and if Ron and Neville were to play their last game of the 3 and draw it would update to:

The idea would be to have both the player names, the games played, and points accrued in a sortable list (personal goal is to get it on one line because why not)

Please let me know what I am missing!

CodePudding user response：

Use this to get unique players, the games they played and points
Adjust the range A1:E if necessary

=ArrayFormula({SPLIT("Player,Played,Points",",");
  QUERY(QUERY({LAMBDA(r,QUERY({SPLIT(INDEX(FLATTEN(TRANSPOSE(
 TRANSPOSE(QUERY(TRANSPOSE(ARRAY_CONSTRAIN(r,1,COLUMNS(r))), " Offset 1")))&"|"&TRANSPOSE(
 QUERY({r}, "Select Col1 where Col1 <> '' Offset 1")))),"|"),FLATTEN(
 FLATTEN(TRANSPOSE(QUERY(TRANSPOSE(QUERY({r}, " Select * where Col1 <> '' Offset 1 ")), " Offset 1 " ))))}," Where Col1<>Col2 "))
 (A1:E)}, " Select min(Col1),count(Col2),count(Col3) Group by Col1")," offset 1",0)} )

CodePudding user response：

result_1

result_2

Use COUNTA with FILTER to calculate the "Played" values,

use SUMPRODUCT with FILTER to get the "Win" values,

use SUM with INDEX / MATCH to calculate the "Draw and Lose" values,

use LAMBDA to make the formulas easier to read,

use SPLIT and BYROW to pack everything up into one cell.

=ArrayFormula(
SPLIT(BYROW(A8:A11,
  LAMBDA(ROW,
   LAMBDA(TOP,LEFT,DATA,
    (COUNTA(FILTER(DATA,LEFT=ROW)) COUNTA(FILTER(DATA,TOP=ROW)))&","&
    ((SUMPRODUCT(FILTER(DATA,LEFT=ROW)=ROW,TRANSPOSE(FILTER(DATA,TOP=ROW))=ROW)*3)
     SUM(INT(((INDEX(DATA,XMATCH(ROW,LEFT,0))<>"")*(INDEX(DATA,XMATCH(ROW,LEFT,0))=ROW))<>TRANSPOSE((INDEX(DATA,,XMATCH(ROW,TOP,0))<>"")*(INDEX(DATA,,XMATCH(ROW,TOP,0))=ROW)))))
   )(B1:E1,A2:A5,B2:E5)
  )
 ),",")
)