how to sum match score

  • Thread starter Thread starter Diana
  • Start date Start date
D

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
 
Diana said:
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.
Click to expand...

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 -----
 
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
 
Hi Diana,

Just an example, assume all the data are in range A1:D5, 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.
 
Back
Top