Google-sheets – Count up the sum of a row, compare with others and return a particular cell value

google sheets

Totally new to formulas that aren't a simple Count or Sum.

I have a sheet containing a list of names and the number of goals they have scored this season. I would like a cell that has the leading goalscorer's name in it.

  Name    Match 1  Match 2  Match 3
Player 1     0        1        0 
Player 2     1        1        3 
Player 3     0        0        0  
Player 4     0        1        0

Leading goalscorer = Player 2

Can anybody please point me in the right direction?

Best Answer

Let's say, you have data on the sheet called "data":

enter image description here

It may contain many players and many matches. This is two dimensional (wide) table. It may grow down and right, so the table has any number or rows or columns.

The main goal is to count sums by lines. Here's sample formula:

=TRANSPOSE(MMULT(ArrayFormula(COLUMN(B1:N1)^0),TRANSPOSE(B2:N21)))

enter image description here

The formula has 2 problems:

  1. it works only for certain ranges, if you add more data, then it will give the wrong result
  2. the formula is placed in the same sheet, it may be replaced by new column of data and it will break the formula.

Let's make another sheet called "report" and make calculations in this sheet. It will solve the problem #2.

We need to know the table dimensions:

enter image description here

the number of players: =COUNTA(data!A:A)-1

the number of games: =COUNTA(data!1:1)-1

and then we can transform the first formula:

=TRANSPOSE(MMULT(ArrayFormula(COLUMN(offset(A1,0,0,1,B2))^0),TRANSPOSE(offset(data!A1,1,1,B1,B2))))

enter image description here

It will solve the problem #1.

The formula for cell A5 to reproduce player names is =OFFSET(data!A1,1,0,B1)

We're almost done!

Now all you need is to use standart functions like query or filter. I'll give the query sample:

=query(data, "select Col1, Col2 order by Col2 desc")

Where data is formula =TRANSPOSE(MMULT(ArrayFormula(COLUMN...

If you want it with single formula:

=QUERY({{"Player";OFFSET(data!A1,1,0,COUNTA(data!A:A)-1)},{"Sum of goals";TRANSPOSE(MMULT(ArrayFormula(COLUMN(offset(A1,0,0,1,COUNTA(data!1:1)-1))^0),TRANSPOSE(offset(data!A1,1,1,COUNTA(data!A:A)-1,COUNTA(data!1:1)-1))))}},"select Col1, Col2 order by Col2 desc")

The result is:

enter image description here

Related Topic