Google Sheets SUMIF Text Formula

Question

Ok so, I will arleady say that im not that good with excel and even worse on google sheets. I would like to know if there is a formula that checks for the name of someone and then sums the points said person got to his total. Thank you very much!

https://docs.google.com/spreadsheets/d/1D4M8Dlao8yFHJulNDff-i2jZJeVGGY_gejbAfzWdhFo/edit?usp=sharing is the link if you want to check it out.

Best to edit your question with a copy of your sheet, or at least some sample data. Otherwise it's pretty hard to help — BigBen
Basically, you see all the players, I need a formula that check if the player has partecipated in the tournament and if he did than it sums the points said player got — UnLegitCombos

kishkin kishkin · Accepted Answer · 2020-04-27T19:32:42

I assume you want Punti tot. (R:R) column to have the sum of all TA Brawl (column C:C), Notte (L:L) and 50 Shades (H:H; in this case any of the two players should be checked, right?) for every player.

You can use put this in R2 (do not forget to remove everything below R2):

={
  "Punti tot.";
  ARRAYFORMULA(
    IF(
      P3:P = ""; ;
          SUMIF(A3:A; P3:P; C3:C)
        + SUMIF(E3:E; P3:P; H3:H)
        + SUMIF(F3:F; P3:P; H3:H)
        + SUMIF(J3:J; P3:P; L3:L)
    )
  )
}

Update: here is another solution using QUERY which is also sorts players by total points:

=QUERY(
  {
    FILTER({A3:A\ C3:C}; A3:A <> "");
    FILTER({E3:E\ H3:H}; E3:E <> "");
    FILTER({F3:F\ H3:H}; F3:F <> "");
    FILTER({J3:J\ L3:L}; J3:J <> "")
  };
  "
    select Col1, SUM(Col2)
    group by Col1
    order by SUM(Col2) desc,
             Col1
    label Col1 'Player',
          SUM(Col2) 'Punti tot.'
  ";
  -1
)

And you might want to use this in O2 for ranking:

={
  "Posizioni Finali";
  ARRAYFORMULA(
    RANK(
      FILTER(Q3:Q; Q3:Q <> "");
      FILTER(Q3:Q; Q3:Q <> "")
    )
  )
}

This way same amount of points gives players the same place. Otherwise hysen would get 9th place and Imurshh would be 10th just because h goes before i.

Google Sheets SUMIF Text Formula

1 Answers