0

I am making a simple excel sheet to keep track of soccer team players scores and saves for the season as in picture. All I need to do is to get the sum of Goals, saves and assists for each players. Played around with SUMIF and SUMPRODUCT, but wasn't able to solve this seemingly small issue.

All I need is the sum of values under corresponding header for each player. To see total goals by a person for all games, total saves for all games etc. I tried commands with logic, if value in Row 2 is 'Goals' then add that to next value under 'Goals text'. Same for Assist etc.

edit: sorry guys pasting the info code generator doesn!t work due to length or I am not tech savvy to make it.

enter image description here

Máté Juhász
  • 21,403
  • 6
  • 54
  • 73
James
  • 3
  • 3
  • specifically, which column/cell is the input, and which column/cell is the output? – p._phidot_ Jun 11 '19 at 01:43
  • (1) Your spreadsheet seems to be inconsistently structured.  This will be a nuisance, and will make it impossible to write a formula that isn’t ugly.  (2) As p._phidot_ says, your sample spreadsheet doesn’t help us if you don’t say what results you want (and I mean *specifically* what values in what cells; *in addition* to your verbal description).  (3) Also, we like it if you show us the formulas that you tried.  (4) [We prefer that you *not* post images of text.](//unix.meta.stackexchange.com/q/4086/23408)  Post a textual representation of your data, as was done  … (Cont’d) – Scott - Слава Україні Jun 11 '19 at 02:37
  • (Cont’d) …  [here](https://superuser.com/q/1289084/150988),  [here](https://superuser.com/q/889201/150988),  [here](https://superuser.com/q/443967/150988),  [here](https://superuser.com/q/684851/150988),  [here](https://superuser.com/q/815333/150988) and [here](//superuser.com/q/892744/150988); use the [Format Text as Table](https://senseful.github.io/text-table) or the [Plain Text Tables generator](http://www.tablesgenerator.com/text_tables) site if you want. As I said before, include the output data you expect from your input data (clearly identifying which is which). – Scott - Слава Україні Jun 11 '19 at 02:37
  • P.S. What does your title mean?  What ‘‘specific text’’ are you talking about? – Scott - Слава Україні Jun 11 '19 at 02:37

2 Answers2

0

You can use SUMIF:

=SUMIF($D$2:$L$2,"goals",$D3:$L3) for goals, and
=SUMIF($D$2:$L$2,"saves",$D3:$L3) for saves

enter image description here

Máté Juhász
  • 21,403
  • 6
  • 54
  • 73
0

I would like to suggest SUMPRODUCT to get Total Goal & Save by each player.

enter image description here

How it works:

  • Formula to get Total Goal in Cell B168: =SUMPRODUCT(($A$161:$A$164=A168)*($B$161:$D$164))

  • Formula to get Total Save in Cell C168: =SUMPRODUCT(($A$161:$A$164=A168)*($F$161:$H$164))

Fill both the Formulas down.

  • Same Formula can be used to get Total Kick & Assist also.
  • Adjust Data Structure & cell references as needed.
Rajesh Sinha
  • 8,995
  • 6
  • 15
  • 35