I have a huge list of matches between clubs and I want to calculate the goals for and the goals against.

I could find a way to calculate the goals for, using:

=SUMIF (A35:A46, "Liverpool", B35:B46) + SUMIF(D35:D46, "Liverpool", C35:C46)

The team being calculated the number of goals is organized like

Newcaste United 1 2 Liverpool (where Newcastle is A1, NewcastleGoals is B1, LiverpoolGoals C1 and Liverpool, D1).

What I need is to find a function that is opposed to SUMIF so I can calculate the goals against Liverpool like SUMIF, instead of collecting the result cell by cell with the sum function.

Basically, the logic would be like the first example, but ignoring "Liverpool" and calculating all of the other strings:

=SUMIF (A1:A38, if not Liverpool, B1:B38) + SUMIF(D1:D38, if not Liverpool, C1:C38)

Thank you!


Have you tried <>?

=SUMIF (A1:A38, "<>Liverpool", B1:B38) + SUMIF(D1:D38, "<>Liverpool", C1:C38)

Not that the <>-symbols should be inside the quotation marks.

  • "<>" stands for "different than"? I'm struggling to find documentation. Gonna try it. Thank you.
    – Luke Perez
    Feb 20 '14 at 18:58
  • Yup, that was exactly what I was looking for. Thank you! :D
    – Luke Perez
    Feb 20 '14 at 19:02
  • Yeah, or. "Not Equal to" Here's documentation on SUMIF: support.google.com/drive/answer/3093583 It's not directly telling you to use '<>', but it's hinting you to use standard operators. From what I can tell Google doesn't seem to have created any extensive documentation covering everything in Spreadsheets. You can read more here (including the basic operators etc.): webapps.stackexchange.com/a/15748/29712 You're very welcome :) Feb 20 '14 at 19:08
  • By the way, if my answer did answer your question, please mark it as the correct answer :) Feb 20 '14 at 19:10
  • Thanks for the remind, I didn't know how to do it - just learned it. Oh, and thank you VERY MUCH for the links. I started studying C# a few weeks ago and I'm in love with Microsoft documentation, so one can just guess my horror trying to use Google Spreadsheets without it. :D
    – Luke Perez
    Feb 20 '14 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.