Tricky "Score Inputting" Problem

[DrBJB]

Hi people,

I am having trouble establishing the correct formula for inputting scores. 5 players in a team (each seeded from 1-5), each playing best of 5-games. Example of results...

0-3 (1)
3-2 (2)
3-0 (3)
3-2 (4)
3-1 (5)
0
0
0

Overall result in new cell needs to formulate to:
12-8

I have tried various SUMPRODUCT, IF, AND formulas... now my brain hurts :-/
The formula needs to ignore the seeded numbers (the numbers in brackets), and the zero's below where other players have not played.

Any help would be greatly appreciated.

Ben

 

[Joe4]

Welcome to the Board!

This would be very easy if each number was in different cells. Can it be done that way?
By the way, if you have a long list of them, you can use Text to Columns to separate them out, first using the space as a delimiter, and then a second time using a "-" as the delimiter.

 

[DrBJB]

Ok Thanks for the advice... guess I was trying to make it too complicated.

Now related to this with the new columns...

I need the sum of G7:G15 to add 4, if the total is above 8. For example:

G7 | 0
G8 | 3
G9 | 3
G10 | 3
G11 | 3
G12 | 0
G13 | 0
G14 | 0
G15 | 0

G17 | 16

This is what I have so far, but doesn't seem to work...? =SUM(G7:G15,IF(G17," >8"),4)

Thanks again.

 

[Joe4]

Try:

=SUM(G7:G15) + IF(SUM(G7:G15) > 8,4,0)

 

[DrBJB]

Thats perfect, seems I had forgotten to tell the computer a few things.

 

[Peter_SSs]

Welcome to the MrExcel board!

An answer to your original question in case you are still interested in that:

Excel 2010 32 bit
	A
1
2	0-3 (1)
3	3-2 (2)
4	3-0 (3)
5	3-2 (4)
6	3-1 (5)
7	0
8	0
9	0
10
11	12-8
Scores

Cell Formulas
Range	Formula
A11	=SUMPRODUCT(--LEFT(A2:A9,FIND("-",A2:A9&"-")-1))&SUMPRODUCT(-MID(SUBSTITUTE(SUBSTITUTE(A2:A9&"-0 ","-"," ")," ",REPT(" ",10)),10,10))