Sum of number on the left in a cell

[veeejay]

In my C column I have some data which looks like this : 0(1), 1(0), 0(0), 2(1), 3(0), 0(0), etc.

I'm trying to sum up the numbers on the LEFT of the parenthesis. This example would nomally return 5.
I tried using this fomula: =sumproduct(--(LEFT(C1:C30),1))) but i get a #VALUE ! error.

Right now I mostly have single digits on the left of the parenhesis and within as well but it's not excluded that I might have double digits in the futur.

Any help?

--BONUS questions : Would it also be possible to count Inside the parenthesis ?
I might be interested in finding out the sum of it too.

Thanks

 

[Eric W]

Leftmost values sum:

=SUM(--IFERROR(LEFT(C1:C30,FIND("(",C1:C30)-1),0))
with Control+Shift+Enter.

Inside the () values sum:

=SUM(-IFERROR(MID(C1:C30,FIND("(",C1:C30),99),0))
with CSE.

 

[Gerald Higgins]

This example would nomally return 5.

Really ?

Shouldn't it be 6 ?
1+2+3

 

[veeejay]

Oops! You got me there!

 

[veeejay]

Leftmost values sum:

=SUM(--IFERROR(LEFT(C1:C30,FIND("(",C1:C30)-1),0))
with Control+Shift+Enter.

Inside the () values sum:

=SUM(-IFERROR(MID(C1:C30,FIND("(",C1:C30),99),0))
with CSE.

Sorry but i'm not sure what CSE is?

In any case for the leftmost value thanks it worked great!

 

[Eric W]

Sorry, CSE is just an abbreviation for Control+Shift+Enter. Glad the leftmost value works for you, it looks for the parenthesis, so it will work if the leftmost value has more than 1 digit.

 

[veeejay]

Alright! It worked! Thanks!

 

[Dave Patton]

SumProduct may work.

Excel 2010
	B	C
1	6	0(1)
2	6	1(0)
3	14	0(0)
4		2(1)
5		3(0)
6		0(0)
7		0(2)
8		0(10)
3a

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(--(LEFT(C1:C8,1)))
B2	{=SUM(--IFERROR(LEFT(C1:C30,FIND("(",C1:C30)-1),0))}
B3	{=SUM(-IFERROR(MID(C1:C30,FIND("(",C1:C30),99),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

The reason I went with the array SUM formulas is because if the range has empty cells at the end, the SUMPRODUCT formula will return a #VALUE error, which is the problem the OP mentioned in the first post.

 

[Eric W]

Although something like this might work if there are no gaps in the values:

=SUMPRODUCT(--LEFT(C1:INDEX(C1:C30,COUNTA(C1:C30))))