SUMIF(S) Question

[BobJCWood]

Hi there.

I am having an issue with what I presume to be a SUMIF formula problem. I have a spreadsheet for a football team that I manage, within it I keep track of all the games they play, the date and time of each match and various player and team statistics.

I have tried to recreate the problem section of my sheet as best as possible below.

Match 1
Date	04/04/2017
Time	20:30
	Score
Our Team	2
Opponent 1	2
Result	Draw
Match 2
Date	11/04/2017
Time	21:00
	Score
Our Team	0
Opponent 2	5
Result	Loss

<tbody>
</tbody>

Now aside from the fact that we've had a poor start to the season, I'm having some trouble calculating some totals.

I currently have a formula that counts how many goals we have scored which is:

=SUMIF($A$20:$A$500, "Our Team", $B$20:$B$500)

This works as expected and returns a value of 2.

I now also want to calculate how many goals we have conceded, so I tried various versions of the following:

=SUMIF($A$20:$A$500, {"<>Our Team","<>Date","<>Time","<>Result"}, $B$20:$B$500)

But that returns a value of 128519.63, so I presume that it is still factoring in the numerical date and time values of the Sum Range cells, despite me attempting to have the formula ignore them.

Am I going about this solution in a reasonable way, or is there a better method?

This sheet will constantly have matches added to it, so a static formula where I don't have to hard code in a simple Sum formula would be preferred if possible please!

The Opposing teams also have the possibility of changing mid-season, so a SUMIF(S) with "Every other Team" listed would also be rather long and clunky, but I could piece this together if this is the most suitable method (I assume there must be a time saver!).

Many thanks,

 

[AlanY]

you're welcome.

i did tried to explain how it works in post#5.

 

[AliGW]

Apologies, but I can't just accept an answer, I like to understand why it is the answer!

Nor can you dismiss an answer if you haven't actually tried it!!!

What you should do is implement the suggestion in your own workbook and check that it gives the right answer. Then, to see how it arrives at that answer, use the Evaluate Formula function in Excel to step through it and see what it's doing.