Formula for adding numbers in brackets to the overall total

[Welders1]

Hi there

We are a junior football team and are wanting to add player appearances into an excel spreadsheet like below.

Player. League (sub). Cup (sub). Total
Goalkeeper. 1(2). 2(1). 6

How can I add the numbers in the brackets as well as the numbers with no brackets into our totals.

Thanks in advance.

[Welders1]

Total is supposed to be 6, wouldn't let me type in the format to help explain.

[nigelbloomy]

This would be very easy if you just format the sheet a little differently.

Make separate columns for "League", "League Sub", "Cup", "Cup Sub". Then can put a =SUM() formula in the totals column. Otherwise parsing that all out in the format you have it is going to be a pain.

[Tony Valko]

Player. League (sub). Cup (sub). Total
Goalkeeper. 1(2). 2(1). 6

Why are there periods/decimal points in there?

You should not do this! Put each piece of information in its own cell.

[Welders1]

Thank you.

[Tony Valko]

Just for the heck of it...

Data Range

	A	B	C	D
1	Player	League (sub)	Cup (sub)	Total
2	Goalkeeper	1(2)	2(1)	6

This formula entered in D2:

=SUMPRODUCT(--MID(SUBSTITUTE(SUBSTITUTE(B2&C2,")",0),"(",0),ROW(INDIRECT("1:"&LEN(B2&C2))),1))

However, I still recommend:

You should not do this! Put each piece of information in its own cell.

EDIT: It should be noted that this only works on single digit numbers!