# Count up the sum of a row, compare with others and return a particular cell value

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?

Let's say, you have data on the sheet called "data": 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)))
`````` 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: 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))))
`````` 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: 