how to sum match score

[Diana]

Hi,

I have this sheet with some scores. I want to sum the matches that a
team has won, lost and draw . For example

A 2 2 B
A 1 0 B
A 1 3 B

Here it should count 1 match won, 1 lost and 1 draw for A, with 4
goals in favor, 5 opposed and a difference of 1 goal.

Thank you very much

 

[Joe User]

Here it should count 1 match won, 1 lost and
1 draw for A, with 4 goals in favor, 5 opposed
and a difference of 1 goal.

For A:
won: =sumproduct(--(B1:B3>C1:C3))
lost: =sumproduct(--(B1:B3<C1:C3))
draw: =sumproduct(--(B1:B3=C1:C3))
goals(B4): =sum(B1:B3)
opposed(C4): =sum(C1:C3)
diff: =abs(B4-C4)

Regarding "diff", I ass-u-me you always want a positive number because you
said "1 goal" for A. I would have said -1 for A, in which case "diff" would
simply be =B4-C4. It makes no sense to me to say -1 if A has more total
goals than B, i.e. =C4-B4, which would give "1 goals" for A in the example
situation.


----- original message -----

 

[Diana]

Hi, Joe

That was awesome, but I made a mistake in my question. Look the table
where I need to count the scores is this way:

Player A 2 2 PLayer B
Player C 1 1 Player D
Player A 1 0 Player D
PLayer E 3 2 Player F
PLayer A 1 3 Player C

So how can I count matches won, lost and draw for each player?

Sorry for the mistake and thank you so much

 

[Smoking]

Hi Diana,

Just an example, assume all the data are in range A15, if I want to count
how many times Player A won, I will use the following formula:

=SUMPRODUCT(--($A$1:$A$5="Player A"),--($B$1:$B$5>$C$1:$C$5))

You can use the logic to deduce how to do the lost and draw ones.